package com.scalar.chiptrack.operations.dataentry.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.WorkWeek;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.AutoGrowingArrayList;
import com.scalar.chiptrack.operations.process.dao.PartNumber;
import com.scalar.chiptrack.operations.process.VendorLocation;
import com.scalar.chiptrack.operations.process.Lot;
import com.scalar.chiptrack.operations.dataentry.LotDetails;
import com.scalar.chiptrack.operations.dataentry.InTransitLots;
import com.scalar.chiptrack.operations.dataentry.DataEntryLot;
import com.scalar.chiptrack.operations.dataentry.rmaReceive.forms.RMAReceiveForm;
import com.scalar.chiptrack.operations.dataentry.inventoryTransit.forms.InventoryTransitForm;
import com.scalar.chiptrack.operations.dataentry.test.TestBinInfo;
import com.scalar.chiptrack.operations.dataentry.lotcorrection.LotCorrection;
import com.scalar.chiptrack.operations.dataentry.lotcorrection.forms.LotChangeForm;
import com.scalar.chiptrack.operations.vendor.Vendor;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.commons.Constants;

import java.util.ArrayList;
import java.util.Calendar;

import java.sql.*;

import org.apache.struts.util.LabelValueBean;

/**
 * Created by IntelliJ IDEA.
 * User: Sudhakar
 * Date: Sep 26, 2006
 * Time: 4:08:28 AM
 */
public class DataEntryDAO implements AdaptorConstants
{

    public static ArrayList getPartNumber(String type) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList partNumberList = null;

        try
        {
            partNumberList = new ArrayList();
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("SELECT PRODUCT.PART_NUMBER,PROCESS_STEP.PROCESSSTEP_ID, PROCESS_STEP.PS_TYPE  FROM PRODUCT,PROCESS_STEP \n" +
                    "WHERE PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PS_TYPE_NAME = ? AND OBSOLETE != 0 ORDER BY PRODUCT.PART_NUMBER");

            pstmt.setString(1, type);
            rs = pstmt.executeQuery();
            PartNumber partNumber;
            while (rs.next())
            {
                partNumber = new PartNumber();
                partNumber.setPartNumber(rs.getString("PART_NUMBER"));
                partNumber.setProcessStepId(rs.getString("PROCESSSTEP_ID"));
                partNumber.setProcessType( rs.getString("PS_TYPE"));

                partNumberList.add(partNumber);
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number by type", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return partNumberList;
    }

    public static ArrayList<PartNumber> getPartNumberByPsType(String type) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<PartNumber> partNumberList = null;

        try
        {
            partNumberList = new ArrayList<PartNumber>();
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("SELECT PRODUCT.PART_NUMBER,PROCESS_STEP.PROCESSSTEP_ID, PROCESS_STEP.PS_TYPE  FROM PRODUCT,PROCESS_STEP \n" +
                    "WHERE PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PS_TYPE = ? AND OBSOLETE != 0 ORDER BY PRODUCT.PART_NUMBER");

            pstmt.setString(1, type);
            rs = pstmt.executeQuery();
            PartNumber partNumber;
            while (rs.next())
            {
                partNumber = new PartNumber();
                partNumber.setPartNumber(rs.getString("PART_NUMBER"));
                partNumber.setProcessStepId(rs.getString("PROCESSSTEP_ID"));
                partNumber.setProcessType( rs.getString("PS_TYPE"));

                partNumberList.add(partNumber);
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number by type", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return partNumberList;
    }

    public static ArrayList getAllLotHistory(String lotId, String psType) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList lotHistoryList = null;                                                                     

        try
        {
            lotHistoryList = new ArrayList();

            conn = DataSource.getConnection();
            if (LOCATION_TYPE_FAB.equalsIgnoreCase(psType) || LOCATION_TYPE_FAB_OPTION.equalsIgnoreCase(psType) || LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase(psType) ||
                    LOCATION_TYPE_WAFER_STORE.equalsIgnoreCase(psType) || LOCATION_TYPE_SORT.equalsIgnoreCase(psType) || Constants.LOCATION_TYPE_BUMPING.equalsIgnoreCase(psType) )
            {
                pstmt = conn.prepareStatement("SELECT WAFER_QTY_IN, STAGE_NAME, CONVERT(VARCHAR,TIMESTAMP, 101) AS TIMESTAMP FROM LOT_TABLE_HISTORY WHERE LOT_ID = ? ORDER BY CAST( TIMESTAMP AS DATETIME) DESC");
            }
            else
            {
                pstmt = conn.prepareStatement("SELECT QTY_IN AS WAFER_QTY_IN ,STAGE_NAME, CONVERT(VARCHAR,TIMESTAMP, 101) AS TIMESTAMP FROM LOT_TABLE_HISTORY WHERE LOT_ID = ? ORDER BY CAST( TIMESTAMP AS DATETIME) DESC");
            }

            pstmt.setString(1, lotId);

            rs = pstmt.executeQuery();

            LotDetails lotDeatils;

            while (rs.next())
            {
                lotDeatils = new LotDetails();
                
                lotDeatils.setWaferQtyIn( rs.getString("WAFER_QTY_IN"));
                lotDeatils.setStageName( rs.getString("STAGE_NAME"));
                lotDeatils.setDateIn( rs.getString("TIMESTAMP") );

                lotHistoryList.add( lotDeatils );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Lot history", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return lotHistoryList;
    }

    public static ArrayList getSelectedLocations(int processStepId) throws ScalarException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList vLocationList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.vendorLocation.readLocations"));
            VendorLocation vLocation = null;
            ps.setInt(1, processStepId);

            rs = ps.executeQuery();

            while (rs.next())
            {
                vLocation = new VendorLocation();
                vLocation.setSelect("on");
                vLocation.setLocationID(rs.getInt("Location_ID") + "");
                vLocation.setLocationName(rs.getString("L_Vendor_Name"));
                vLocation.setPriority(rs.getInt("PSLT_Priority") + "");
                vLocation.setSplit(rs.getInt("PSLT_Split") + "");
                vLocation.setCapacityWeek(rs.getInt("PSLT_Capacity") + "");
                vLocationList.add(vLocation);
            }

        }
        catch (SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Reading Vendor Location failed ", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException se)
                {
                    throw new ScalarException("SqlException Occurred ..");
                }
            }
            if (ps != null)
            {
                try
                {
                    ps.close();
                }
                catch (SQLException se)
                {
                    throw new ScalarException("SqlException Occurred ..");
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException se)
                {
                    throw new ScalarException("SqlException Occurred ..");
                }
            }
        }

        return vLocationList;
    }

    /**
     *
     * @param processStepId
     * @param locationId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getRunningLotNumbersByPSTypeName(String processStepId, String locationId ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList lotNumberList = new ArrayList();
        Lot lot = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareCall("select Lot_Table.Lot_ID,Lot_Number,Lot_Current_Status from Lot_Table, Process_Step where Lot_Table.Processstep_Id = Process_Step.Processstep_Id and Lot_Table.Processstep_Id=? and Lot_Table.Lot_Current_Status <> 'Close'  and Location_Id=? ORDER BY Lot_Number");
            pstmt.setString(1, processStepId);
            pstmt.setString(2, locationId);

            rs = pstmt.executeQuery();

            while (rs.next())
            {
                lot = new Lot();
                lot.setLotId( rs.getString("Lot_ID"));
                lot.setLotNumber( rs.getString("Lot_Number"));
                lot.setStatus( rs.getString("Lot_Current_Status"));
                lotNumberList.add(lot);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Lot Numbers from database", sqle);
        }

        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return lotNumberList;
    }


    /**
     *
     * @param processStepId
     * @param locationId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getBumpStoreLots(String processStepId, String locationId ) throws ScalarException {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList lotNumberList = new ArrayList();
        Lot lot = null;

        try
        {
           // System.out.println("psTypeName = " + psTypeName);

            conn = DataSource.getConnection();
            pstmt = conn.prepareCall("");
            pstmt.setString(1, processStepId);
            pstmt.setString(2, locationId);
           // pstmt.setString(3, psTypeName);

            rs = pstmt.executeQuery();

            while (rs.next())
            {
                lot = new Lot(rs.getString(1), rs.getString(2));
                lot.setStatus(rs.getString(3));
                lotNumberList.add(lot);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Ruuning Lot Numbers from database", sqle);
        }

        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return lotNumberList;
    }

    /**
     *
     * @param lotId
     * @return
     * @throws Exception
     */
    public static LotDetails getFabLotInfo( String  lotId ) throws Exception
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        LotDetails fabLotDetails    = new LotDetails();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(" SELECT * FROM FN_GET_LOT_INFO( ?, ?, ? ) ");
            pstmt.setString(1, lotId);
            pstmt.setString(2, LOCATION_TYPE_FAB );
            pstmt.setString(3, LOCATION_TYPE_FAB );

            rs = pstmt.executeQuery();
            if (rs.next())
            {
                fabLotDetails.setLotNumber(rs.getString("Lot_Number"));
                fabLotDetails.setLotStatus(rs.getString("Lot_Current_Status"));
                fabLotDetails.setWaferQtyIn(rs.getString("Wafer_Qty_In"));
                fabLotDetails.setWaferQtyOut(rs.getString("Wafer_Qty_Out"));
                fabLotDetails.setDateIn(rs.getString("Date_In"));
                fabLotDetails.setDateOut(rs.getString("Date_Out"));
                fabLotDetails.setPoId(rs.getString("Po_No"));
                fabLotDetails.setShopOrderNo(rs.getString("Shop_Order_No"));
                fabLotDetails.setFabLotNumber(rs.getString("Fab_Lot_Number"));
                fabLotDetails.setVendorLotNumber(rs.getString("Vendor_Lot_Number"));
                fabLotDetails.setWaferIds(rs.getString("Wafer_Id"));
                fabLotDetails.setLotCategory(rs.getString("Lot_Category"));
                fabLotDetails.setDateCode(rs.getString("Lot_Date_Code"));
                fabLotDetails.setComments( rs.getString("Lot_Comments"));
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get fab lot data from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return fabLotDetails;
    }

    /**
     *
     * @param lotId
     * @return
     * @throws Exception
     */
    /*public static LotDetails getBumpLotInfo(String lotId) throws Exception
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        LotDetails bumpLotDetails = new LotDetails();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(" SELECT * FROM FN_GET_LOT_INFO( ?, ? ) ");
            pstmt.setString(1, lotId);
            pstmt.setString(2, LOCATION_TYPE_BUMPING );

            rs = pstmt.executeQuery();
            if (rs.next())
            {
                bumpLotDetails.setLotNumber(rs.getString("Lot_Number"));
                bumpLotDetails.setLotStatus(rs.getString("Lot_Current_Status"));
                bumpLotDetails.setWaferQtyIn(rs.getString("Wafer_Qty_In"));
                bumpLotDetails.setWaferQtyOut(rs.getString("Wafer_Qty_Out"));
                bumpLotDetails.setDateIn(rs.getString("Date_In"));
                bumpLotDetails.setDateOut(rs.getString("Date_Out"));
                bumpLotDetails.setPoId(rs.getString("Po_No"));
                bumpLotDetails.setShopOrderNo(rs.getString("Shop_Order_No"));
                bumpLotDetails.setFabLotNumber(rs.getString("Fab_Lot_Number"));
                bumpLotDetails.setVendorLotNumber(rs.getString("Vendor_Lot_Number"));
                bumpLotDetails.setWaferIds(rs.getString("Wafer_Id"));
                bumpLotDetails.setLotCategory(rs.getString("Lot_Category"));
                bumpLotDetails.setDateCode(rs.getString("Lot_Date_Code"));
                bumpLotDetails.setComments(rs.getString("Lot_Comments"));
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Bump lot data from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return bumpLotDetails;
    }*/

    /**
     * @param lotId
     * @return
     * @throws Exception
     */
    /*public static LotDetails getCpLotInfo(String lotId) throws Exception
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        LotDetails cpLotDetails = new LotDetails();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(" SELECT * FROM FN_GET_LOT_INFO( ?, ? ) ");
            pstmt.setString(1, lotId);
            pstmt.setString(2, LOCATION_TYPE_CP );

            rs = pstmt.executeQuery();
            if (rs.next())
            {
                cpLotDetails.setLotNumber(rs.getString("Lot_Number"));
                cpLotDetails.setLotStatus(rs.getString("Lot_Current_Status"));
                cpLotDetails.setWaferQtyIn(rs.getString("Wafer_Qty_In"));
                cpLotDetails.setWaferQtyOut(rs.getString("Wafer_Qty_Out"));
                cpLotDetails.setDateIn(rs.getString("Date_In"));
                cpLotDetails.setDateOut(rs.getString("Date_Out"));
                cpLotDetails.setPoId(rs.getString("Po_No"));
                cpLotDetails.setShopOrderNo(rs.getString("Shop_Order_No"));
                cpLotDetails.setFabLotNumber(rs.getString("Fab_Lot_Number"));
                cpLotDetails.setVendorLotNumber(rs.getString("Vendor_Lot_Number"));
                cpLotDetails.setWaferIds(rs.getString("Wafer_Id"));
                cpLotDetails.setLotCategory(rs.getString("Lot_Category"));
                cpLotDetails.setDateCode(rs.getString("Lot_Date_Code"));
                cpLotDetails.setComments(rs.getString("Lot_Comments"));
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get CP Lot data from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return cpLotDetails;
    }*/

    /**
     *
     * @param processStep_id
     * @return
     * @throws ScalarException
     */
    public static ArrayList getTransitLots(String processStep_id, String fabLotNo) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList transitLotList = new ArrayList();
        ResultSet rs = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT LT.LOT_NUMBER, SL.SHIPPEDLOT_ID , SL.WAFER_ID \n" +
                    "FROM LOT_TABLE LT, SHIPPED_LOT SL WHERE SL.LOT_ID = LT.LOT_ID \n" +
                    "AND SL.STATUS = 'TRANSIT' AND SL.SHIP_STATUS <= 0 and  \n" +
                    "LT.PROCESSSTEP_ID = ? and LT.Fab_Lot_Number = ? ");
            pstmt.setString(1, processStep_id);
            pstmt.setString(2, fabLotNo);
            rs = pstmt.executeQuery();
            LotDetails inTransitLots = null;

            while (rs.next())
            {
                inTransitLots = new LotDetails();
                inTransitLots.setLotNumber( rs.getString("LOT_NUMBER"));
                inTransitLots.setShippedLotId(rs.getString("SHIPPEDLOT_ID"));
                inTransitLots.setWaferIds( rs.getString("WAFER_ID") );
                transitLotList.add(inTransitLots);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load all Lotnumbers from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return transitLotList;
    }

    public static ArrayList getDieBankLots(String processStep_id, String location_id, String fabLotNo) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList transitLotList = new ArrayList();
        ResultSet rs = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT DISTINCT LOT_NUMBER, LT.LOT_ID FROM LOT_TABLE LT, SORT_BIN_RELEASE_TABLE SBRL WHERE LT.LOT_ID = SBRL.LOT_ID AND PROCESSSTEP_ID = ? AND LOCATION_ID = ? AND FAB_LOT_NUMBER = ? ");
            pstmt.setString(1, processStep_id);
            pstmt.setString(2, location_id);
            pstmt.setString(3, fabLotNo);
            rs = pstmt.executeQuery();
            LotDetails inTransitLots = null;

            while (rs.next())
            {
                inTransitLots = new LotDetails();
                inTransitLots.setLotNumber( rs.getString("LOT_NUMBER"));
                inTransitLots.setLotId(rs.getString("LOT_ID" ) );
                transitLotList.add(inTransitLots);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load all Lotnumbers from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return transitLotList;
    }
    /**
     *
     * @param shippedLot_id
     * @return
     * @throws ScalarException
     */
    public static LotDetails getTransitInfo(String shippedLot_id) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        LotDetails inTransitLots = new LotDetails();

        int shippedLot = -1;

        try
        {
            shippedLot = Integer.parseInt(shippedLot_id);
        }
        catch (NumberFormatException npe)
        {
            shippedLot = -1;
        }

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT Lt.Lot_number, LT.Po_Number, LT.Shop_Order_Number, CONVERT( VARCHAR, SL.SHIP_DATE, 101) SHIP_DATE, SL.SHIP_QTY, SL.Ship_Good_Die_Qty, SL.SL_Customer_PO, SL.SL_Customer_SO, SL.SL_Invoice_Number , SL.SL_NextLocation, SL.SL_ETA, SL.SL_ETD, SL.WAFER_ID, SL.SL_Flight_Number,  LT.LOT_COMMENTS FROM LOT_TABLE LT, SHIPPED_LOT SL WHERE LT.LOT_ID = SL.LOT_ID AND SL.SHIPPEDLOT_ID = ? ");
            pstmt.setInt(1, shippedLot);
            rs = pstmt.executeQuery();

            if (rs.next())
            {
                inTransitLots.setLotNumber(rs.getString("Lot_number"));
                inTransitLots.setPoId(rs.getString("Po_Number"));
                inTransitLots.setShopOrderNo(rs.getString("Shop_Order_Number"));
                inTransitLots.setShipDate(rs.getString("SHIP_DATE"));
                inTransitLots.setShipQty(rs.getString("SHIP_QTY"));
                inTransitLots.setShipDieQty(rs.getString("Ship_Good_Die_Qty"));
                inTransitLots.setPoNumber(rs.getString("SL_Customer_PO"));
                inTransitLots.setCustomerSo(rs.getString("SL_Customer_SO"));
                inTransitLots.setInvoiceNo(rs.getString("SL_Invoice_Number"));
                inTransitLots.setNextLocation(rs.getString("SL_NextLocation"));
                inTransitLots.setEta(rs.getString("SL_ETA"));
                inTransitLots.setEtd(rs.getString("SL_ETD"));
                inTransitLots.setWaferIds(rs.getString("WAFER_ID"));
                inTransitLots.setFilghtNo(rs.getString("SL_Flight_Number"));
                inTransitLots.setComments(rs.getString("LOT_COMMENTS"));
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Shipped Lot Information from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return inTransitLots;
    }

    public static ArrayList getDieBankLotInfo(String shippedLot_id) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList dieBankLotsList = new ArrayList();
        LotDetails  dieBankLots = null;
        int shippedLot = -1;

        try
        {
            shippedLot = Integer.parseInt(shippedLot_id);
        }
        catch (NumberFormatException npe)
        {
            shippedLot = -1;
        }

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT WAFER_ID, BIN_1_BALANCE_QTY FROM LOT_TABLE LT, SORT_BIN_RELEASE_TABLE SBRL WHERE LT.LOT_ID = SBRL.LOT_ID AND LT.LOT_ID = ? ");
            pstmt.setInt(1, shippedLot);
            rs = pstmt.executeQuery();

            while (rs.next() )
            {
                dieBankLots = new LotDetails();
                
                dieBankLots.setDieQtyIn( rs.getString("BIN_1_BALANCE_QTY"));
                dieBankLots.setWaferIds( rs.getString("WAFER_ID") );

                dieBankLotsList.add( dieBankLots );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Shipped Lot Information from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return dieBankLotsList;
    }


    public static ArrayList getDieBankBinLotInfo(String lot_id) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList dieBankLotsList = new ArrayList();
        TestBinInfo  binInfo = null;
        int lotId = -1;

        try
        {
            lotId = Integer.parseInt(lot_id);
        }
        catch (NumberFormatException npe)
        {
            lotId = -1;
        }

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT WAFER_ID, BIN_1_BALANCE_QTY FROM LOT_TABLE LT, SORT_BIN_RELEASE_TABLE SBRL WHERE LT.LOT_ID = SBRL.LOT_ID AND LT.LOT_ID = ? AND SBRL.BIN_1_BALANCE_QTY > 0 ORDER BY CAST(WAFER_ID AS INT) ");
            pstmt.setInt(1, lotId);
            rs = pstmt.executeQuery();

            while (rs.next() )
            {
                binInfo = new TestBinInfo();

                binInfo.setBinQty( rs.getString("BIN_1_BALANCE_QTY"));
                binInfo.setWaferId( rs.getString("WAFER_ID") );

                dieBankLotsList.add( binInfo );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Shipped Lot Information from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return dieBankLotsList;
    }
    /**
     * 
     * @param dbTransaction
     * @param fabDataEntryList
     * @throws ScalarException
     */
    public static void saveDataEntryFabLot(DBTransaction dbTransaction, ArrayList fabDataEntryList) throws ScalarException
    {

        Connection conn            = null;
        PreparedStatement pstmt    = null;
        boolean success            = false;
        LotDetails dataEntryfabLot;

        if ((fabDataEntryList == null) || (fabDataEntryList.isEmpty()))
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_data_entry_fab_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < fabDataEntryList.size(); i++)
            {
                dataEntryfabLot = (LotDetails) fabDataEntryList.get(i);

                pstmt.setString(1, dataEntryfabLot.getProcessStepId());
                pstmt.setString(2, dataEntryfabLot.getLocationId());
                pstmt.setString(3, dataEntryfabLot.getFabLotNumber());
                pstmt.setString(4, dataEntryfabLot.getWaferQtyIn());
                pstmt.setString(5, dataEntryfabLot.getWaferQtyOut());
                pstmt.setString(6, dataEntryfabLot.getPoId());
                pstmt.setString(7, dataEntryfabLot.getShopOrderNo());
                pstmt.setString(8, dataEntryfabLot.getDateIn());
                pstmt.setString(9, dataEntryfabLot.getDateOut());
                pstmt.setString(10, dataEntryfabLot.getVendorName());
                pstmt.setString(11, dataEntryfabLot.getWaferIds());
                pstmt.setString(12, dataEntryfabLot.getLotCategory());
                pstmt.setString(13, dataEntryfabLot.getVendorLotNumber());
                pstmt.setString(14, dataEntryfabLot.getDateCode());
                pstmt.setString(15, dataEntryfabLot.getLotStatus());
                pstmt.setString(16, dataEntryfabLot.getComments());


                pstmt.addBatch();
            }

            pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save FAB lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    /**
     * @param dbTransaction
     * @param bumpDataEntryList
     * @throws ScalarException
     */
    public static void saveDataEntryBumpLot( DBTransaction dbTransaction, ArrayList bumpDataEntryList) throws ScalarException
    {

        Connection conn             = null;
        PreparedStatement pstmt     = null;
        boolean success             = false;
        LotDetails dataEntryBumpLot;

        if ((bumpDataEntryList == null) || (bumpDataEntryList.isEmpty()))
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_data_entry_bump_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < bumpDataEntryList.size(); i++)
            {
                dataEntryBumpLot = (LotDetails) bumpDataEntryList.get(i);

                pstmt.setString(1, dataEntryBumpLot.getProcessStepId());
                pstmt.setString(2, dataEntryBumpLot.getLocationId());
                pstmt.setString(3, dataEntryBumpLot.getFabLotNumber());
                pstmt.setString(4, dataEntryBumpLot.getWaferQtyIn());
                pstmt.setString(5, dataEntryBumpLot.getWaferQtyOut());
                pstmt.setString(6, dataEntryBumpLot.getPoId());
                pstmt.setString(7, dataEntryBumpLot.getShopOrderNo());
                pstmt.setString(8, dataEntryBumpLot.getDateIn());
                pstmt.setString(9, dataEntryBumpLot.getDateOut());
                pstmt.setString(10, dataEntryBumpLot.getVendorName());
                pstmt.setString(11, dataEntryBumpLot.getWaferIds());
                pstmt.setString(12, dataEntryBumpLot.getLotCategory());
                pstmt.setString(13, dataEntryBumpLot.getVendorLotNumber());
                pstmt.setString(14, dataEntryBumpLot.getDateCode());
                pstmt.setString(15, dataEntryBumpLot.getLotStatus());
                pstmt.setString(16, dataEntryBumpLot.getComments());

                pstmt.addBatch();
            }

            pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save BUMP lot information", sqle);
        }

        finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    /**
     *
     * @param dbTransaction
     * @param cpDataEntryList
     * @throws ScalarException
     */
    public static void saveDataEntrySortLot( DBTransaction dbTransaction, ArrayList cpDataEntryList) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails dataEntryCpLot;

        if ((cpDataEntryList == null) || (cpDataEntryList.isEmpty()))
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_data_entry_sort_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < cpDataEntryList.size(); i++)
            {
                dataEntryCpLot = (LotDetails) cpDataEntryList.get(i);

                pstmt.setString(1, dataEntryCpLot.getProcessStepId());
                pstmt.setString(2, dataEntryCpLot.getLocationId());
                pstmt.setString(3, dataEntryCpLot.getFabLotNumber());
                pstmt.setString(4, dataEntryCpLot.getWaferQtyIn());
                pstmt.setString(5, dataEntryCpLot.getWaferQtyOut());
                pstmt.setString(6, dataEntryCpLot.getPoId());
                pstmt.setString(7, dataEntryCpLot.getShopOrderNo());
                pstmt.setString(8, dataEntryCpLot.getDateIn());
                pstmt.setString(9, dataEntryCpLot.getDateOut());
                pstmt.setString(10, dataEntryCpLot.getVendorName());
                pstmt.setString(11, dataEntryCpLot.getWaferIds());
                pstmt.setString(12, dataEntryCpLot.getLotCategory());
                pstmt.setString(13, dataEntryCpLot.getVendorLotNumber());
                pstmt.setString(14, dataEntryCpLot.getDateCode());
                pstmt.setString(15, dataEntryCpLot.getLotStatus());
                pstmt.setString(16, dataEntryCpLot.getComments());

                pstmt.addBatch();
            }

            pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save CP Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    /**
     * 
     * @param processStepId
     * @param locationId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getPOandShopOrderNo(String processStepId, String locationId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList poNumberList = new ArrayList();
        ResultSet rs = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(" SELECT PO_ID, PO_NO, SHOP_ORDER_NO FROM PO_TABLE WHERE SO_STATUS IN ('RELEASED', 'STARTED')\n" +
                    "AND SO_PART_NUMBER = ( SELECT PART_NUMBER FROM PRODUCT \n" +
                    "WHERE PROCESSSTEP_ID = ? )AND PO_VENDOR_ID = ( SELECT VENDOR_ID FROM LOCATION WHERE LOCATION_ID = ? ) ");

            pstmt.setString(1, processStepId);
            pstmt.setString(2, locationId);

            rs = pstmt.executeQuery();
            LotDetails poDetails = null;

            while (rs.next())
            {
                poDetails = new LotDetails();
                poDetails.setPoId( rs.getString("PO_ID"));
                poDetails.setPoNumber(rs.getString("PO_NO"));
                poDetails.setShopOrderNo(rs.getString("SHOP_ORDER_NO"));
                poNumberList.add(poDetails);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load all PO Numbers from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return poNumberList;
    }
    /**
     *
     * @param purchaseOrderId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getShopOrderNo(String purchaseOrderId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList shopOrderList = new ArrayList();
        ResultSet rs = null;
                
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT SHOP_ORDER_NO FROM PO_TABLE WHERE PO_ID = ?  ");
            pstmt.setString(1, purchaseOrderId);
            rs = pstmt.executeQuery();
            LotDetails shopOrderDetails = null;

            while (rs.next())
            {
                shopOrderDetails = new LotDetails();
                shopOrderDetails.setShopOrderNo( rs.getString("SHOP_ORDER_NO"));
                shopOrderList.add(shopOrderDetails);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load all Lotnumbers from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return shopOrderList;
    }
    /**
     *
     * @param dbTransaction
     * @param dataEntryTransitLot
     * @throws ScalarException
     */
    public static void saveDataEntryTransitLot(DBTransaction dbTransaction, LotDetails dataEntryTransitLot, String userName ) throws ScalarException
    {

        Connection conn             = null;
        PreparedStatement pstmt     = null;
        boolean success             = false;
        //LotDetails dataEntryTransitLot;

        if(dataEntryTransitLot == null)
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_data_entry_shipped_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            //for (int i = 0; i < transitDataEntryList.size(); i++)
            //{
                //dataEntryTransitLot = (LotDetails) transitDataEntryList.get(i);

                pstmt.setString( 1, dataEntryTransitLot.getProcessStepId() );
                pstmt.setString( 2, dataEntryTransitLot.getLocationId() );
                pstmt.setString( 3, dataEntryTransitLot.getProcessType() );
                pstmt.setString( 4, dataEntryTransitLot.getFabLotNumber() );
                pstmt.setString( 5, dataEntryTransitLot.getVendorLotNumber() );
                pstmt.setString( 6, dataEntryTransitLot.getPoId() );
                pstmt.setString( 7, dataEntryTransitLot.getShopOrderNo() );
                pstmt.setString( 8, dataEntryTransitLot.getShipQty() );
                pstmt.setString( 9, dataEntryTransitLot.getShipDate() );
                pstmt.setString( 10, dataEntryTransitLot.getInvoiceNo() );
                pstmt.setString( 11, dataEntryTransitLot.getFilghtNo() );
                pstmt.setString( 12, dataEntryTransitLot.getEtd() );
                pstmt.setString( 13, dataEntryTransitLot.getEta() );
                pstmt.setString( 14, dataEntryTransitLot.getCustomerSo() );
                pstmt.setString( 15, dataEntryTransitLot.getNextLocation() );
                pstmt.setString( 16, dataEntryTransitLot.getWaferIds() );
                pstmt.setString( 17, dataEntryTransitLot.getShippedLotId() );
                pstmt.setString( 18, dataEntryTransitLot.getShipDieQty() );
                pstmt.setString( 19, dataEntryTransitLot.getComments() );
                pstmt.setString( 20, userName );


                pstmt.executeUpdate();
           // }

            //pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Transit lot information", sqle);
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    /**
     * @param lotId
     * @return
     * @throws Exception
     */
    public static LotDetails getAssemblyLotInfo(String lotId) throws Exception
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        LotDetails assemblyLotDetails = new LotDetails();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(" SELECT * FROM FN_GET_LOT_INFO( ?, ?, ? ) ");
            pstmt.setString(1, lotId);
            pstmt.setString(2, LOCATION_TYPE_ASSEMBLY );
            pstmt.setString(3, LOCATION_TYPE_ASSEMBLY );

            rs = pstmt.executeQuery();
            if (rs.next())
            {
                assemblyLotDetails.setLotNumber(rs.getString("Lot_Number"));
                assemblyLotDetails.setLotStatus(rs.getString("Lot_Current_Status"));
                assemblyLotDetails.setDieQtyIn(rs.getString("Die_Qty_In"));
                assemblyLotDetails.setDieQtyOut(rs.getString("Die_Qty_Out"));
                assemblyLotDetails.setDateIn(rs.getString("Date_In"));
                assemblyLotDetails.setDateOut(rs.getString("Date_Out"));
                assemblyLotDetails.setPoId(rs.getString("Po_No"));
                assemblyLotDetails.setShopOrderNo(rs.getString("Shop_Order_No"));
                assemblyLotDetails.setFabLotNumber(rs.getString("Fab_Lot_Number"));
                assemblyLotDetails.setVendorLotNumber(rs.getString("Vendor_Lot_Number"));
                assemblyLotDetails.setWaferIds(rs.getString("Wafer_Id"));
                assemblyLotDetails.setLotCategory(rs.getString("Lot_Category"));
                assemblyLotDetails.setDateCode(rs.getString("Lot_Date_Code"));
                assemblyLotDetails.setComments(rs.getString("Lot_Comments"));
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Assembly Lot data from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return assemblyLotDetails;
    }

    /**
     *
     * @param dbTransaction
     * @param assemblyDataEntryList
     * @throws ScalarException
     */
    public static void saveDataEntryAssemblyLot( DBTransaction dbTransaction, ArrayList assemblyDataEntryList, String userName) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails dataEntryAssemblyLot;

        if ((assemblyDataEntryList == null) || (assemblyDataEntryList.isEmpty()))
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_data_entry_assembly_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < assemblyDataEntryList.size(); i++)
            {
                dataEntryAssemblyLot = (LotDetails) assemblyDataEntryList.get(i);

                pstmt.setString(1, dataEntryAssemblyLot.getProcessStepId());
                pstmt.setString(2, dataEntryAssemblyLot.getLocationId());
                pstmt.setString(3, dataEntryAssemblyLot.getFabLotNumber());
                pstmt.setString(4, dataEntryAssemblyLot.getDieQtyIn());
                pstmt.setString(5, dataEntryAssemblyLot.getDieQtyOut());
                pstmt.setString(6, dataEntryAssemblyLot.getPoId());
                pstmt.setString(7, dataEntryAssemblyLot.getShopOrderNo());
                pstmt.setString(8, dataEntryAssemblyLot.getDateIn());
                pstmt.setString(9, dataEntryAssemblyLot.getDateOut());
                pstmt.setString(10, dataEntryAssemblyLot.getVendorName());
                pstmt.setString(11, dataEntryAssemblyLot.getWaferIds());
                pstmt.setString(12, dataEntryAssemblyLot.getLotCategory());
                pstmt.setString(13, dataEntryAssemblyLot.getVendorLotNumber());
                pstmt.setString(14, dataEntryAssemblyLot.getDateCode());
                pstmt.setString(15, dataEntryAssemblyLot.getLotStatus());
                pstmt.setString(16, dataEntryAssemblyLot.getComments());
                pstmt.setString(17, userName);
                pstmt.setInt(18, 0 ); //PREV SHIPPED LOT ID
                pstmt.setInt(19, 0 ); //PREV LOT ID

                pstmt.addBatch();
            }

            pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Assembly Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    /**
     *
     * @param dbTransaction
     * @param invDataEntryList
     * @throws ScalarException
     */
    public static void saveDataEntryInventoryLot( DBTransaction dbTransaction, ArrayList invDataEntryList, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails dataEntryInventoryLot;

        if ((invDataEntryList == null) || (invDataEntryList.isEmpty()))
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_data_entry_inventory_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < invDataEntryList.size(); i++)
            {
                int lotId;
                dataEntryInventoryLot = (LotDetails) invDataEntryList.get(i);
                try {
                 lotId = Integer.parseInt( dataEntryInventoryLot.getLotId() );
                }
                catch(Exception e)
                {
                    lotId = 0;
                }
                                                
                pstmt.setString(1, dataEntryInventoryLot.getProcessStepId());
                pstmt.setString(2, dataEntryInventoryLot.getLocationId());
                pstmt.setString(3, dataEntryInventoryLot.getFabLotNumber());
                pstmt.setString(4, lotId > 0  ? dataEntryInventoryLot.getLotNumber() : dataEntryInventoryLot.getVendorLotNumber() );
                pstmt.setString(5, dataEntryInventoryLot.getWaferQtyIn());
                pstmt.setString(6, dataEntryInventoryLot.getWaferQtyOut());
                pstmt.setString(7, dataEntryInventoryLot.getDieQtyIn());
                pstmt.setString(8, dataEntryInventoryLot.getDieQtyOut());
                pstmt.setString(9, dataEntryInventoryLot.getDateIn());
                pstmt.setString(10, dataEntryInventoryLot.getDateOut());
                pstmt.setString(11, dataEntryInventoryLot.getVendorName());
                pstmt.setString(12, dataEntryInventoryLot.getWaferIds());
                pstmt.setString(13, dataEntryInventoryLot.getLotCategory());
                pstmt.setString(14, dataEntryInventoryLot.getDateCode());
                pstmt.setString(15, dataEntryInventoryLot.getLotStatus());
                pstmt.setString(16, dataEntryInventoryLot.getProcessType());
                pstmt.setString(17, dataEntryInventoryLot.getComments());
                pstmt.setString(18, dataEntryInventoryLot.getAssemblyVendor() );
                pstmt.setString(19, userName );
                pstmt.setInt(20, 0 ); //PREV SHIPPED LOT ID
                pstmt.setInt(21, 0 ); //PREV LOT ID

                pstmt.addBatch();
            }

            pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Inventory Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void saveDataEntryInventoryLot( DBTransaction dbTransaction, LotDetails lotDetails ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

       /*int processStepIdInt    = 0;
        int locationIdInt       = 0;
        int waferQtyInInt       = 0;
        int waferQtyOutInt      = 0;
        int dieQtyInInt         = 0;
        int dieQtyOutInt        = 0;

        try
        {
            processStepIdInt = Integer.parseInt( lotDetails.getProcessStepId() );
            locationIdInt = Integer.parseInt( lotDetails.getLocationId() );
        }
        catch( NumberFormatException nfe ){  }

        try
        {
            waferQtyInInt = Integer.parseInt( lotDetails.getWaferQtyIn() );
            waferQtyOutInt = Integer.parseInt( lotDetails.getWaferQtyOut() );
        }
        catch( NumberFormatException nfe ){  }

        try
        {
            dieQtyInInt = Integer.parseInt( lotDetails.getDieQtyIn() );
            dieQtyOutInt = Integer.parseInt( lotDetails.getDieQtyOut() );
        }
        catch( NumberFormatException nfe ){  }*/


        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_data_entry_inventory_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            pstmt.setString (1, lotDetails.getProcessStepId() );
            pstmt.setString (2, lotDetails.getLocationId() );
            pstmt.setString (3, lotDetails.getFabLotNumber() );
            pstmt.setString (4, lotDetails.getVendorLotNumber() );
            pstmt.setString (5, lotDetails.getWaferQtyIn() );
            pstmt.setString (6, lotDetails.getWaferQtyOut() );
            pstmt.setString (7, lotDetails.getDieQtyIn() );
            pstmt.setString (8, lotDetails.getDieQtyOut() );
            pstmt.setString (9, lotDetails.getDateIn() );
            pstmt.setString (10, lotDetails.getDateOut() );
            pstmt.setString (11, lotDetails.getVendorName() );
            pstmt.setString (12, lotDetails.getWaferIds() );
            pstmt.setString (13, lotDetails.getLotCategory() );
            pstmt.setString (14, lotDetails.getDateCode() );
            pstmt.setString (15, lotDetails.getLotStatus() );
            pstmt.setString (16, lotDetails.getProcessType() );
            pstmt.setString (17, lotDetails.getComments() );

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Inventory Lot information", sqle);
        }

        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

     /**
     * @param lotId
     * @return
     * @throws Exception
     */
    public static LotDetails getInventoryLotInfo(String lotId/*, String psType, String psTypeName*/) throws Exception
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        LotDetails invLotDetails = new LotDetails();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(" SELECT * FROM FN_GET_LOT_INFO( ?, ?, ? ) ");
            pstmt.setString(1, lotId);
            pstmt.setString(2, "INVENTORY");
            pstmt.setString(3, "INVENTORY");

            rs = pstmt.executeQuery();
            if (rs.next())
            {
                invLotDetails.setLotNumber(rs.getString("Lot_Number"));
                invLotDetails.setLotStatus(rs.getString("Lot_Current_Status"));
                invLotDetails.setWaferQtyIn(rs.getString("Wafer_Qty_In"));
                invLotDetails.setWaferQtyOut(rs.getString("Wafer_Qty_Out"));
                invLotDetails.setDieQtyIn(rs.getString("Die_Qty_In"));
                invLotDetails.setDieQtyOut(rs.getString("Die_Qty_Out"));
                invLotDetails.setDateIn(rs.getString("Date_In"));
                invLotDetails.setDateOut(rs.getString("Date_Out"));
                invLotDetails.setPoId(rs.getString("Po_No"));
                invLotDetails.setShopOrderNo(rs.getString("Shop_Order_No"));
                invLotDetails.setFabLotNumber(rs.getString("Fab_Lot_Number"));
                invLotDetails.setVendorLotNumber(rs.getString("Vendor_Lot_Number"));
                invLotDetails.setWaferIds(rs.getString("Wafer_Id"));
                invLotDetails.setLotCategory(rs.getString("Lot_Category"));
                invLotDetails.setDateCode(rs.getString("Lot_Date_Code"));
                invLotDetails.setComments(rs.getString("Lot_Comments"));
                invLotDetails.setAssemblyVendor(rs.getString("Assembly_Vendor"));
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Inventory Lot data from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return invLotDetails;
    }
    /**
     *
     * @return
     * @throws ScalarException
     */
    public static ArrayList getBomPartNumber() throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList partNumberList    = null;
        PartNumber partNumber;

        try
        {
            partNumberList = new ArrayList();
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("SELECT PART_NUMBER,PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP  WHERE P_TYPE='BOM' AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID ORDER BY PART_NUMBER");

            rs = pstmt.executeQuery();

            while (rs.next())
            {
                partNumber = new PartNumber();
                partNumber.setPartNumber(rs.getString("PART_NUMBER"));
                partNumber.setProcessStepId(rs.getString("PROCESSSTEP_ID"));

                partNumberList.add(partNumber);
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number ", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return partNumberList;
    }

    /**
     *
     * @param type
     * @return
     * @throws ScalarException
     */
    public static ArrayList<PartNumber> getInvPartNumber(String type) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<PartNumber> partNumberList = null;

        try
        {
            partNumberList = new ArrayList<PartNumber>();
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("SELECT PRODUCT.PART_NUMBER,PROCESS_STEP.PROCESSSTEP_ID, PROCESS_STEP.PS_TYPE  FROM PRODUCT,PROCESS_STEP \n" +
                    "WHERE PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PS_TYPE_Name = ? AND OBSOLETE != 0  ORDER BY PRODUCT.PART_NUMBER  ");

            pstmt.setString(1, type);
            rs = pstmt.executeQuery();
            PartNumber partNumber;

            while (rs.next())
            {
                partNumber = new PartNumber();
                partNumber.setPartNumber(rs.getString("PART_NUMBER"));
                partNumber.setProcessStepId(rs.getString("PROCESSSTEP_ID"));
                partNumber.setProcessType(rs.getString("PS_TYPE"));

                partNumberList.add(partNumber);
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Bump Store Part Number by type", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return partNumberList;
    }

    public static ArrayList loadAllVendors() throws ScalarException
	{
		ArrayList vendorList 	= new ArrayList();
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;

		try
		{
			conn	= DataSource.getConnection();
			pstmt 	= conn.prepareStatement( "Select V_Name, Vendor_Id from Vendor where V_IS_Deleted = 'N' Order by V_Name" );

			rs 		= pstmt.executeQuery();

			while ( rs.next() )
			{
				vendorList.add( new LabelValueBean ( rs.getString ( "V_Name" ), ""+rs.getInt( "Vendor_Id" ) ) );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
            throw new ScalarException ("Could not get the all the vendors from the database.");
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return vendorList;
	}
    public static LotDetails getBOMInventoryLotInfo(String lotId) throws Exception
        {
            Connection conn = null;
            ResultSet rs = null;
            PreparedStatement pstmt = null;
            LotDetails invLotDetails = new LotDetails();

            try
            {
                conn = DataSource.getConnection();
                pstmt = conn.prepareStatement(" SELECT * FROM FN_GET_LOT_INFO( ?, ?, ? ) ");
                pstmt.setString(1, lotId);
                pstmt.setString(2, LOCATION_TYPE_FINISHED_GOODS);
                pstmt.setString(3, LOCATION_TYPE_FG);

                rs = pstmt.executeQuery();
                if (rs.next())
                {
                    invLotDetails.setLotNumber(rs.getString("Lot_Number"));
                    invLotDetails.setLotStatus(rs.getString("Lot_Current_Status"));
                    invLotDetails.setWaferQtyIn(rs.getString("Wafer_Qty_In"));
                    invLotDetails.setWaferQtyOut(rs.getString("Wafer_Qty_Out"));
                    invLotDetails.setDieQtyIn(rs.getString("Die_Qty_In"));
                    invLotDetails.setDieQtyOut(rs.getString("Die_Qty_Out"));
                    invLotDetails.setDateIn(rs.getString("Date_In"));
                    invLotDetails.setDateOut(rs.getString("Date_Out"));
                    invLotDetails.setPoId(rs.getString("Po_No"));
                    invLotDetails.setShopOrderNo(rs.getString("Shop_Order_No"));
                    invLotDetails.setFabLotNumber(rs.getString("Fab_Lot_Number"));
                    invLotDetails.setVendorLotNumber(rs.getString("Vendor_Lot_Number"));
                    invLotDetails.setWaferIds(rs.getString("Wafer_Id"));
                    invLotDetails.setLotCategory(rs.getString("Lot_Category"));
                    invLotDetails.setDateCode(rs.getString("Lot_Date_Code"));
                    invLotDetails.setComments(rs.getString("Lot_Comments"));
                }

            }
            catch (Exception sqle)
            {
                sqle.printStackTrace();
                throw new ScalarException("Could not get Inventory Lot data from database", sqle);
            }
            finally
            {
                if (rs != null)
                {
                    try
                    {
                        rs.close();
                    }
                    catch (SQLException ignore)
                    {
                    }
                }
                if (pstmt != null)
                {
                    try
                    {
                        pstmt.close();
                    }
                    catch (SQLException ignore)
                    {
                    }
                }
                if (conn != null)
                {
                    try
                    {
                        conn.close();
                    }
                    catch (SQLException ignore)
                    {
                    }
                }
            }
            return invLotDetails;
        }

    public static boolean isLotNumberExists ( DBTransaction dbTransaction, int processStepId, int locationId, String lotNumber, String psTypeName) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        boolean isLotNumberExists = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

           /* pstmt = conn.prepareStatement("SELECT LOT_NUMBER FROM LOT_TABLE LT, PROCESS_STEP PS WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID = ? " +
                    " AND LT.LOT_CURRENT_STATUS IN ('Running', 'Hold') AND LOCATION_ID = ? AND LT.LOT_NUMBER = ?");*/
            String query = "SELECT LOT_NUMBER FROM LOT_TABLE LT, PROCESS_STEP PS, LOCATION L WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID ";

            if (processStepId > 0)
            {
                query = query + " AND LT.PROCESSSTEP_ID = " + processStepId;
            }
            if (locationId > 0)
            {
                query = query + " AND LT.LOCATION_ID = " + locationId;
            }
            if (lotNumber != null )
            {
                query = query + " AND LOT_NUMBER = '" + lotNumber + "'";
            }
            if ( psTypeName != null )
            {
                query = query + " AND LT.LOCATION_ID IN (SELECT LOCATION_ID FROM LOCATION WHERE L_TYPE = '" + psTypeName + "')";
            }
            //System.out.println("query = " + query);
            pstmt = conn.prepareStatement(query);

            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                isLotNumberExists = true;
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Lot Numbers from database", sqle);
        }

        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return isLotNumberExists;
    }


    public static ArrayList<InTransitLots> loadAllTransitLots( String processType, String processStepIds, int locationId , String nextProcessType, int vendorId, int lotId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList<InTransitLots> shipLotsList = new ArrayList<InTransitLots>();
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( "{ CALL SP_LOAD_ALL_TRANSIT_LOTS_SEARCH( ?, ?, ?, ?, ?, ? ) }" );
            pstmt.setString( 1, processType );
            pstmt.setString( 2, processStepIds );
            pstmt.setInt( 3, locationId );
            pstmt.setString( 4, nextProcessType );
            pstmt.setInt( 5, vendorId );
            pstmt.setInt( 6, lotId );

            rset = pstmt.executeQuery();
            
            InTransitLots inTransitLots = null;
            WorkWeek wk = WorkWeek.getCurrentWorkWeekInstance();
            int currentwk = wk.getCurrentWWNumber();
            String yearAsDigit  = (wk.getWorkWeekYear()+"").substring(2);

            while ( rset.next() )
            {
                inTransitLots = new InTransitLots();

                inTransitLots.setProcessStepId( rset.getString( "PROCESSSTEP_ID" ) );
                inTransitLots.setPartNumber( rset.getString( "Part_Number" ) );
                inTransitLots.setLotNumber( rset.getString( "Lot_Number" ) );
                inTransitLots.setFabLotNumber( rset.getString( "Fab_Lot_Number" ) );
                inTransitLots.setProcessType( rset.getString("Ps_Type") );
                
                if ( rset.getString("DATE_CODE") != null )
                {
                    inTransitLots.setDateCode(rset.getString("DATE_CODE") );
                }
                else
                {
                    inTransitLots.setDateCode( yearAsDigit + (currentwk < 10 ? "0"+currentwk : currentwk+"") );
                }
                
                inTransitLots.setVendorName( rset.getString( "Previous_Loc") );

                if ( rset.getString( "NEXT_LOC") != null && rset.getString( "NEXT_LOC").indexOf( VENDOR_NAME_MITSUI ) != -1 )
                {
                    inTransitLots.setNextLocation( VENDOR_NAME_MITSUI) ;
                }
                else if (rset.getString( "NEXT_LOC") != null && (rset.getString( "NEXT_LOC").toUpperCase().indexOf(VENDOR_NAME_OSEP ) != -1 || rset.getString( "NEXT_LOC").indexOf("Orient Semi." ) !=- 1  ))
                {
                    inTransitLots.setNextLocation( VENDOR_NAME_OSEP ) ;
                }
                else
                {
                    inTransitLots.setNextLocation( rset.getString("NEXT_LOC") ) ;
                }
                inTransitLots.setNextProcessType( rset.getString("NEXT_PROCESS") );
                inTransitLots.setShipDate( rset.getString( "Ship_Date" ) );
                inTransitLots.setShipQty( rset.getString( "Ship_Qty" ) );
                inTransitLots.setGrossDiePerWafer( rset.getInt("GDPW") );
                inTransitLots.setGrossDie( rset.getInt( "Gross_Die" ) );
                inTransitLots.setShippedLotID( rset.getString( "ShippedLot_Id" ) );
                inTransitLots.setWaferId( rset.getString("Wafer_ID") );
                inTransitLots.setLotId( rset.getInt("Lot_id") );
                inTransitLots.setVendorCode( rset.getString("VENDOR_CODE") );
                inTransitLots.setDieBankLotNo( rset.getString("DB_LOTNO") );
                inTransitLots.setDieBankprocessStepId( rset.getInt("DB_PROCESSSTEP_ID") );
                
                if ( LOCATION_TYPE_DIE_BANK.equalsIgnoreCase( inTransitLots.getProcessType() ) )
                {
                    if ( inTransitLots.getNextProcessType() == null )
                    {
                        inTransitLots.setNextProcessType( LOCATION_TYPE_ASSEMBLY );
                    }
                    inTransitLots.setGdpwString( rset.getString("BIN_QTY_STR") );
                }
                else if ( inTransitLots.getNextProcessType() == null && LOCATION_TYPE_ASSEMBLY.equalsIgnoreCase( inTransitLots.getProcessType() ) )
                {
                    inTransitLots.setNextProcessType( LOCATION_TYPE_FT );
                }
                else if ( inTransitLots.getNextProcessType() == null && LOCATION_TYPE_FT.equalsIgnoreCase( inTransitLots.getProcessType() ) )
                {
                    inTransitLots.setNextProcessType( LOCATION_TYPE_FPO );
                }
                else if ( inTransitLots.getNextProcessType() == null && LOCATION_TYPE_FPO.equalsIgnoreCase( inTransitLots.getProcessType() ) )
                {
                    inTransitLots.setNextProcessType( LOCATION_TYPE_TAPE_REEL );
                }
                else if ( inTransitLots.getNextProcessType() == null && LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase(inTransitLots.getProcessType() ) )
                {
                    inTransitLots.setNextProcessType( LOCATION_TYPE_FG );
                }

                shipLotsList.add( inTransitLots );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all Ship Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return shipLotsList;
    }

    public static ArrayList<InTransitLots> loadAllTransitLotsByPsType( String processType, int processStepId, int locationId , String nextProcessType, int vendorId, int lotId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList<InTransitLots> shipLotsList = new ArrayList<InTransitLots>();
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( "{ CALL SP_LOAD_ALL_TRANSIT_LOTS_SEARCH_BY_PSTYPE( ?, ?, ?, ?, ?, ? ) }" );
            pstmt.setString( 1, processType );
            pstmt.setInt( 2, processStepId );
            pstmt.setInt( 3, locationId );
            pstmt.setString( 4, nextProcessType );
            pstmt.setInt( 5, vendorId );
            pstmt.setInt( 6, lotId );

            rset = pstmt.executeQuery();

            InTransitLots inTransitLots = null;

            while ( rset.next() )
            {
                inTransitLots = new InTransitLots();

                inTransitLots.setProcessStepId( rset.getString( "PROCESSSTEP_ID" ) );
                inTransitLots.setPartNumber( rset.getString( "Part_Number" ) );
                inTransitLots.setLotNumber( rset.getString( "Lot_Number" ) );
                inTransitLots.setFabLotNumber( rset.getString( "Fab_Lot_Number" ) );
                inTransitLots.setProcessType( rset.getString("Ps_Type") );
                inTransitLots.setVendorName( rset.getString( "Previous_Loc") );
                inTransitLots.setNextLocation( rset.getString("NEXT_LOC") ) ;
                inTransitLots.setNextProcessType( rset.getString("NEXT_PROCESS") );
                inTransitLots.setShipDate( rset.getString( "Ship_Date" ) );
                inTransitLots.setShipQty( rset.getString( "Ship_Qty" ) );
                inTransitLots.setRecivedShipQty( rset.getString( "Ship_Qty" ) );
                inTransitLots.setGrossDiePerWafer( rset.getInt("GDPW") );
                inTransitLots.setGrossDie( rset.getInt( "Gross_Die" ) );
                inTransitLots.setShippedLotID( rset.getString( "ShippedLot_Id" ) );
                inTransitLots.setWaferId( rset.getString("Wafer_ID") );
                inTransitLots.setLotId( rset.getInt("Lot_id") );
                inTransitLots.setGdpwString( rset.getString("BIN_QTY_STR") );
                inTransitLots.setDieBankprocessStepId( rset.getInt("DB_PROCESSSTEP_ID") );

                shipLotsList.add( inTransitLots );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all Ship Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return shipLotsList;
    }

    /**
     *
     * @param dbTransaction
     * @param testDataEntryList
     * @throws ScalarException
     */
    public static void saveDataEntryTestLot( DBTransaction dbTransaction, ArrayList testDataEntryList, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails dataEntryTestLot;

        if ((testDataEntryList == null) || (testDataEntryList.isEmpty()))
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_data_entry_test_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < testDataEntryList.size(); i++)
            {
                dataEntryTestLot = (LotDetails) testDataEntryList.get(i);

                pstmt.setString(1, dataEntryTestLot.getProcessStepId());
                pstmt.setString(2, dataEntryTestLot.getLocationId());
                pstmt.setString(3, dataEntryTestLot.getFabLotNumber());
                pstmt.setString(4, dataEntryTestLot.getDieQtyIn());
                pstmt.setString(5, dataEntryTestLot.getDieQtyOut());
                pstmt.setString(6, dataEntryTestLot.getPoId());
                pstmt.setString(7, dataEntryTestLot.getShopOrderNo());
                pstmt.setString(8, dataEntryTestLot.getDateIn());
                pstmt.setString(9, dataEntryTestLot.getDateOut());
                pstmt.setString(10, dataEntryTestLot.getVendorName());
                pstmt.setString(11, dataEntryTestLot.getWaferIds());
                pstmt.setString(12, dataEntryTestLot.getLotCategory());
                pstmt.setString(13, dataEntryTestLot.getVendorLotNumber());
                pstmt.setString(14, dataEntryTestLot.getDateCode());
                pstmt.setString(15, dataEntryTestLot.getLotStatus());
                pstmt.setString(16, dataEntryTestLot.getComments());
                pstmt.setString(17, userName);
                pstmt.setInt(18, 0 ); //PREV SHIPPED LOT ID
                pstmt.setInt(19, 0 ); //PREV LOT ID
                pstmt.executeUpdate();
                            
            }

           // pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Test Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    /**
     *
     * @return
     * @throws ScalarException
     */
    public static ArrayList getNextLocations() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList transitLotList = new ArrayList();
        ResultSet rs = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT DISTINCT V_NAME FROM VENDOR where V_IS_DELETED = 'N' ");

            rs = pstmt.executeQuery();
            Vendor vendor = null;

            while (rs.next())
            {
                vendor = new Vendor();
                vendor.setVendorName( rs.getString("V_NAME"));
                transitLotList.add(vendor);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load all Next Locations from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return transitLotList;
    }

    /**
     * 
     * @param dbTransaction
     * @param psTypeName
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadAllVendorsByProcess( DBTransaction dbTransaction, String psTypeName ) throws ScalarException
    {
        Connection        conn      = null;
        PreparedStatement pstmt     = null;
        ResultSet         rs        = null;
        ArrayList         locations = new ArrayList();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT L_VENDOR_NAME AS VENDOR_NAME, LOCATION_ID FROM LOCATION WHERE L_TYPE = ? AND L_IS_DELETED = 'N' ORDER BY  L_VENDOR_NAME" );
            pstmt.setString( 1, psTypeName);

            rs = pstmt.executeQuery();
            while ( rs.next() )
            {
                locations.add( new LabelValueBean( rs.getString("VENDOR_NAME"), rs.getInt("LOCATION_ID")+"" ) );
            }
        }
        catch(SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not load all Vendors By Process", sql);
        }
        finally
        {
            if(rs != null)
            {
                try{ rs.close();}catch(Exception e){}
            }
            if(pstmt != null)
            {
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null)
            {
                try{ conn.close(); }catch(Exception e){}
            }
        }
        return locations;
    }

    public static ArrayList loadAllVendorsByPsType( DBTransaction dbTransaction, String psType ) throws ScalarException
    {
        Connection        conn      = null;
        PreparedStatement pstmt     = null;
        ResultSet         rs        = null;
        ArrayList         locations = new ArrayList();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT L_VENDOR_NAME AS VENDOR_NAME, LOCATION_ID FROM LOCATION WHERE L_TYPE = (select Base_Process_Type FROM PROCESS_TYPES_TABLE WHERE Process_Type = ? ) AND L_IS_DELETED = 'N' ORDER BY  L_VENDOR_NAME" );
            pstmt.setString( 1, psType );

            rs = pstmt.executeQuery();
            while ( rs.next() )
            {
                locations.add( new LabelValueBean( rs.getString("VENDOR_NAME"), rs.getInt("LOCATION_ID")+"" ) );
            }
        }
        catch(SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not load all Vendors By Process", sql);
        }
        finally
        {
            if(rs != null)
            {
                try{ rs.close();}catch(Exception e){}
            }
            if(pstmt != null)
            {
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null)
            {
                try{ conn.close(); }catch(Exception e){}
            }
        }
        return locations;
    }


public static boolean checkDuplicateLots(DBTransaction dbTransaction, LotCorrection lotCorrection) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("select lot_number from lot_table(NOLOCK) where lot_id <> ? and lot_number = ? and processstep_id = ? and location_id = ?");
            pstmt.setString(1, lotCorrection.getLotId());
            pstmt.setString(2, lotCorrection.getLotNumber());
            pstmt.setString(3, lotCorrection.getProcessStepId());
            pstmt.setString(4, lotCorrection.getLocationId());

            rs = pstmt.executeQuery();

            if (rs.next())
            {
                success = true;
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Error while checking for duplicate lots", e);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException se)
                {

                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException se)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException se)
                {
                }
            }
        }
        return success;
    }
    public static void saveLotCorrectionLot( DBTransaction dbTransaction, ArrayList lotCorrectionList) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotCorrection lotCorrectionLot;

        if ((lotCorrectionList == null) || (lotCorrectionList.isEmpty()))
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call SP_SAVE_LOTS_CORRECTION( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");
            for (int i = 0; i < lotCorrectionList.size(); i++)
            {
                lotCorrectionLot = (LotCorrection) lotCorrectionList.get(i);

                pstmt.setString ( 1, lotCorrectionLot.getLotId());
                pstmt.setString ( 2, lotCorrectionLot.getLotNumber());
                pstmt.setString ( 3, lotCorrectionLot.getLotStatus());
                pstmt.setString ( 4, lotCorrectionLot.getProcessType());
                pstmt.setString ( 5, lotCorrectionLot.getProcessStepId());
                pstmt.setString ( 6, lotCorrectionLot.getLocationId());
                pstmt.setString ( 7, lotCorrectionLot.getQuantityIn());
                pstmt.setString ( 8, lotCorrectionLot.getQuantityOut());
                pstmt.setString ( 9, lotCorrectionLot.getDateIn());
                pstmt.setString ( 10, lotCorrectionLot.getDateOut());
                pstmt.setString ( 11, lotCorrectionLot.getDateCode());
                pstmt.setString ( 12, lotCorrectionLot.getWaferQtyIn());
                pstmt.setString ( 13, lotCorrectionLot.getWaferQtyOut());
                pstmt.setString ( 14, lotCorrectionLot.getFabLotNumber());
                pstmt.setString ( 15, lotCorrectionLot.getVendorLotNumber());
                pstmt.setString ( 16, lotCorrectionLot.getLotCategory() );
                pstmt.setString ( 17, lotCorrectionLot.getLotComments());
                pstmt.setString ( 18, lotCorrectionLot.getWaferIds());
                pstmt.setInt    ( 19, lotCorrectionLot.isHold() ? 0 : 1 );
                pstmt.addBatch();
            }

            pstmt.executeBatch();
            if (dbTransaction == null)
            {
                conn.commit();
            }
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not Save Lot Correction information", sqle );
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if ( !success )
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {

                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {

                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {

                }
            }
        }
    }
    public static LotCorrection getLotInfoByLotIdprocessType(String lotId, String processType) throws Exception
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        LotCorrection lotCorrectionLotDetails = new LotCorrection();

        try
        {
            conn = DataSource.getConnection();
            //pstmt = conn.prepareStatement("SELECT LOT_NUMBER, LOT_CURRENT_STATUS, QTY_IN,  QTY_OUT, WAFER_QTY_IN, WAFER_QTY_OUT,CONVERT(VARCHAR,DATE_IN,101) AS Date_In, CONVERT(VARCHAR,DATE_OUT,101) AS Date_out, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS FROM LOT_TABLE where LOT_TABLE.LOT_ID = ?");
            pstmt = conn.prepareStatement("SELECT LT.LOT_ID AS LOT_ID, LOT_NUMBER, LOT_CURRENT_STATUS, QTY_IN,  QTY_OUT, WAFER_QTY_IN, WAFER_QTY_OUT,CONVERT(VARCHAR,DATE_IN,101) AS Date_In, CONVERT(VARCHAR,DATE_OUT,101) AS Date_out, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, LOT_MANUAL_HOLD, (CASE WHEN PS.PS_TYPE_NAME = 'FAB' THEN (SELECT WAFER_ID FROM FAB_LOT WHERE LOT_ID = LT.LOT_ID) WHEN PS.PS_TYPE_NAME = 'Fab Option' THEN (SELECT WAFER_ID FROM FAB_LOT WHERE LOT_ID = LT.LOT_ID) ELSE NULL END) AS WAFER_IDS FROM LOT_TABLE LT,PROCESS_STEP PS WHERE  lt.processstep_id = ps.processstep_id AND LT.LOT_ID=?");
            pstmt.setString(1, lotId);
            rs = pstmt.executeQuery();
            if (rs.next())
            {
                lotCorrectionLotDetails.setLotId(lotId);
                lotCorrectionLotDetails.setLotNumber ( rs.getString("LOT_NUMBER") );
                lotCorrectionLotDetails.setLotStatus ( rs.getString("LOT_CURRENT_STATUS") );
                lotCorrectionLotDetails.setQuantityIn ( rs.getString("QTY_IN") );
                lotCorrectionLotDetails.setQuantityOut ( rs.getString("QTY_OUT") );
                lotCorrectionLotDetails.setWaferQtyIn ( rs.getString ( "WAFER_QTY_IN" ) );
                lotCorrectionLotDetails.setWaferQtyOut ( rs.getString("WAFER_QTY_OUT") );
                lotCorrectionLotDetails.setDateIn ( rs.getString("Date_In") );
                lotCorrectionLotDetails.setDateOut ( rs.getString("Date_Out") );
                lotCorrectionLotDetails.setFabLotNumber ( rs.getString("FAB_LOT_NUMBER") );
                lotCorrectionLotDetails.setVendorLotNumber ( rs.getString("VENDOR_LOT_NUMBER") );
                lotCorrectionLotDetails.setLotCategory ( rs.getString("LOT_CATEGORY") );
                lotCorrectionLotDetails.setDateCode ( rs.getString("LOT_DATE_CODE") );
                lotCorrectionLotDetails.setLotComments ( rs.getString("LOT_COMMENTS") );
                if (rs.getInt("LOT_MANUAL_HOLD") == 1)
                {
                    lotCorrectionLotDetails.setHold(false);
                }
                else
                {
                    lotCorrectionLotDetails.setHold(true);
                }
                lotCorrectionLotDetails.setWaferIds ( rs.getString("WAFER_IDS") );
                lotCorrectionLotDetails.setProcessType ( processType );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Lot Information of " + lotId , sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return lotCorrectionLotDetails;
    }
    public static void changeLotProduct(DBTransaction dbTransaction, LotChangeForm lotChangeForm) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        ResultSet rs			= null;
        if ((lotChangeForm == null) )
        {
             return;
        }

         try
         {
             if (dbTransaction == null)
             {
                 conn = DataSource.getConnection();
             }
             else
             {
                 conn = dbTransaction.getConnection();
             }
            pstmt = conn.prepareCall ( "{ call sp_change_lot_product ( ?, ?, ? ) }" );
            pstmt.setInt( 1, Integer.parseInt( lotChangeForm.getLotId() ) );
            pstmt.setInt( 2, Integer.parseInt( lotChangeForm.getNewProcessStepId() ) );
            pstmt.setInt( 3, lotChangeForm.getExistingInventory() );

            pstmt.executeUpdate();
            if (dbTransaction == null)
            {
                conn.commit();
            }
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save the change Product for Lot " + lotChangeForm.getLotId(), sqle );
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if ( !success )
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {

                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {

                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {

                }
            }
        }
        return ;
    }

    public static void changeLotLocation(DBTransaction dbTransaction, LotChangeForm lotChangeForm) throws ScalarException
   {
           Connection conn         = null;
           PreparedStatement pstmt = null;
           boolean success         = false;
           ResultSet rs			= null;
           if ((lotChangeForm == null) )
           {
                return;
           }
            try
            {
                if (dbTransaction == null)
                {
                    conn = DataSource.getConnection();
                }
                else
                {
                    conn = dbTransaction.getConnection();
                }
			    pstmt = conn.prepareCall ( "{ call sp_change_lot_location ( ?, ?, ? ) }" );
			    pstmt.setInt( 1, Integer.parseInt( lotChangeForm.getLotId() ) );
			    pstmt.setInt( 2, Integer.parseInt( lotChangeForm.getNewLocationId() ) );
			    pstmt.setInt( 3, 1 );// This third parameter is kept for flag what to do with the previous lot As of now not implemented

			    pstmt.executeUpdate();
			    success = true;
                if (dbTransaction == null)
                {
                    conn.commit();
                }
                success = true;
            }
            catch (Exception sqle)
            {
                sqle.printStackTrace();
                throw new ScalarException( "Could not save the change Product for Lot " + lotChangeForm.getLotId(), sqle );
            }
            finally
            {
                if (dbTransaction == null)
                {
                    try
                    {
                        if (conn != null)
                        {
                            if ( !success )
                            {
                                conn.rollback();
                            }
                        }
                    }
                    catch (SQLException ignore)
                    {

                    }
                }
                if (pstmt != null)
                {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {

                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {

                }
            }
        }
        return ;
   }
   public static ArrayList getAllLotNumbersByPSTypeName(String processStepId, String locationId ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList lotNumberList = new ArrayList();
        Lot lot = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT LOT_TABLE.LOT_ID,LOT_NUMBER,LOT_CURRENT_STATUS FROM LOT_TABLE(NOLOCK), PROCESS_STEP(NOLOCK) WHERE LOT_TABLE.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID AND LOT_TABLE.PROCESSSTEP_ID=? AND LOCATION_ID=? ORDER BY LOT_NUMBER");
            pstmt.setString(1, processStepId);
            pstmt.setString(2, locationId);

            rs = pstmt.executeQuery();

            while (rs.next())
            {
                lot = new Lot(rs.getString(1), rs.getString(2));
                lot.setStatus(rs.getString(3));
                lotNumberList.add(lot);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Lot Numbers from database", sqle);
        }

        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return lotNumberList;
    }

    /**
     * @param processStepId
     * @param locationId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getRunningFabLotNumbersByPSTypeName(String processStepId, String locationId) throws ScalarException
    {
        Connection conn         = null;
        ResultSet rs            = null;
        PreparedStatement pstmt = null;
        ArrayList lotNumberList = new ArrayList();
        Lot lot                 = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareCall("SELECT DISTINCT FAB_LOT_NUMBER FROM LOT_TABLE(NOLOCK), PROCESS_STEP(NOLOCK) \n" +
                    "WHERE LOT_TABLE.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID AND LOT_TABLE.PROCESSSTEP_ID=? AND \n" +
                    "LOCATION_ID=? ORDER BY FAB_LOT_NUMBER");
            pstmt.setString(1, processStepId);
            pstmt.setString(2, locationId);

            rs = pstmt.executeQuery();

            while (rs.next())
            {
                lot = new Lot();
                lot.setFabLotnumber(rs.getString("Fab_Lot_Number"));
                lotNumberList.add(lot);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Fab Lot Numbers from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return lotNumberList;
    }

    /**
     *
     * @param processStepId
     * @param locationId
     * @param fabLotNo
     * @return
     * @throws ScalarException
     */
    public static ArrayList getRunningandHoldLotNumbersByPSTypeName(String processStepId, String locationId, String fabLotNo) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList lotNumberList = new ArrayList();
        Lot lot = null;

        try
        {
            conn = DataSource.getConnection();
            if( fabLotNo != null)
            {
                pstmt = conn.prepareCall("select Lot_Table.Lot_ID,Lot_Number,Lot_Current_Status from Lot_Table, Process_Step where Lot_Table.Processstep_Id = Process_Step.Processstep_Id and Lot_Table.Processstep_Id=? and Lot_Table.Lot_Current_Status != 'Close' and Location_Id=?  and Fab_Lot_Number = ? AND LOT_SPLIT = 1 ORDER BY Lot_Number");
                pstmt.setString(1, processStepId);
                pstmt.setString(2, locationId);
                pstmt.setString(3, fabLotNo);

            }
            else
            {
                pstmt = conn.prepareCall("select Lot_Table.Lot_ID,Lot_Number,Lot_Current_Status from Lot_Table, Process_Step where Lot_Table.Processstep_Id = Process_Step.Processstep_Id and Lot_Table.Processstep_Id=? and Lot_Table.Lot_Current_Status != 'Close' and Location_Id=?  AND LOT_SPLIT = 1 ORDER BY Lot_Number");
                pstmt.setString(1, processStepId);
                pstmt.setString(2, locationId);

            }

            rs = pstmt.executeQuery();

            while (rs.next())
            {
                lot = new Lot();
                lot.setLotId(rs.getString("Lot_ID"));
                lot.setLotNumber(rs.getString("Lot_Number"));
                lot.setStatus(rs.getString("Lot_Current_Status"));
                lotNumberList.add(lot);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Lot Numbers from database", sqle);
        }

        finally {
            if (rs != null) {
                try {
                    rs.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        lotNumberList.size();
        return lotNumberList;
    }
   
    public static ArrayList getTransitFabLots(int processStep_id, int location_Id) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList transitLotList = new ArrayList();
        ResultSet rs = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT DISTINCT LT.FAB_LOT_NUMBER FROM LOT_TABLE LT, SHIPPED_LOT SL WHERE SL.LOT_ID = LT.LOT_ID AND SL.STATUS = 'TRANSIT' AND SL.SHIP_STATUS <= 0 and LT.PROCESSSTEP_ID = ?  and Lt.Location_Id = ?");
            pstmt.setInt( 1, processStep_id );
            pstmt.setInt( 2, location_Id );
            rs = pstmt.executeQuery();
            LotDetails inTransitLots = null;

            while (rs.next())
            {
                inTransitLots = new LotDetails();
                inTransitLots.setFabLotNumber( rs.getString("Fab_LOT_NUMBER"));
                transitLotList.add(inTransitLots);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load all Lotnumbers from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return transitLotList;
    }

    public static ArrayList getDieBankFabLots(String processStep_id, String location_id ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList transitLotList = new ArrayList();
        ResultSet rs = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT DISTINCT FAB_LOT_NUMBER FROM LOT_TABLE LT, SORT_BIN_RELEASE_TABLE SBRL WHERE LT.LOT_ID = SBRL.LOT_ID AND PROCESSSTEP_ID = ? AND LOCATION_ID = ? AND BIN_1_BALANCE_QTY > 0 ");
            pstmt.setString(1, processStep_id);
             pstmt.setString(2, location_id);
            rs = pstmt.executeQuery();
            LotDetails inTransitLots = null;

            while (rs.next())
            {
                inTransitLots = new LotDetails();
                inTransitLots.setFabLotNumber( rs.getString("FAB_LOT_NUMBER") );
                transitLotList.add(inTransitLots);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load all Lotnumbers from the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return transitLotList;
    }

    public static ArrayList getBoardInvPartNumber(String type) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList partNumberList = null;

        try
        {
            partNumberList = new ArrayList();
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("SELECT PRODUCT.PART_NUMBER,PROCESS_STEP.PROCESSSTEP_ID, PROCESS_STEP.PS_TYPE, P_TYPE  FROM PRODUCT,PROCESS_STEP \n" +
                    "WHERE PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND (P_TYPE='BOARD' OR P_TYPE='KIT')   AND  PS_TYPE_Name = ? AND OBSOLETE != 0  ORDER BY  P_TYPE , PRODUCT.PART_NUMBER  ");

            pstmt.setString(1, type);
            rs = pstmt.executeQuery();
            PartNumber partNumber;

            boolean kitLabelAdded = false;
            boolean boardLabelAdded = false;
            while (rs.next())
            {
                if("BOARD".equalsIgnoreCase(rs.getString("P_TYPE"))&& !boardLabelAdded)
                {
                    PartNumber kitpartNumber = new PartNumber();
                    kitpartNumber.setPartNumber("--BOARD--");
                    kitpartNumber.setProcessStepId("-1");
                    partNumberList.add(kitpartNumber);
                    boardLabelAdded= true ;
                }
                partNumber = new PartNumber();
                partNumber.setPartNumber(rs.getString("PART_NUMBER"));
                partNumber.setProcessStepId(rs.getString("PROCESSSTEP_ID"));
                partNumber.setProcessType(rs.getString("PS_TYPE"));
                if("KIT".equalsIgnoreCase(rs.getString("P_TYPE"))&& !kitLabelAdded)
                {
                    PartNumber kitpartNumber = new PartNumber();
                    kitpartNumber.setPartNumber("--KIT--");
                    kitpartNumber.setProcessStepId("-2");
                    partNumberList.add(kitpartNumber);
                    kitLabelAdded= true ;
                }
                partNumberList.add(partNumber);
            }
            
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get" + type + "  Part Number by type", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return partNumberList;
    }

    public static LotDetails getBoardInventoryLotInfo(String lotId, String processType) throws Exception
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        LotDetails invLotDetails = new LotDetails();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(" SELECT * FROM FN_GET_LOT_INFO( ?, ?, ? ) ");
            pstmt.setString(1, lotId);
            if (("Finished Goods".equalsIgnoreCase(processType))||("DIE BANK".equalsIgnoreCase(processType)) )
            {
                    pstmt.setString(2, LOCATION_TYPE_WAFER_STORE);
                    pstmt.setString(3, LOCATION_TYPE_WAFER_BANK);
            }
            else if (("ASSEMBLY".equalsIgnoreCase(processType)) || ("TEST".equalsIgnoreCase(processType)) )
            {
                pstmt.setString(2, LOCATION_TYPE_ASSEMBLY);
                pstmt.setString(3, LOCATION_TYPE_ASSEMBLY);
            }

            rs = pstmt.executeQuery();
            if (rs.next())
            {
                invLotDetails.setLotNumber(rs.getString("Lot_Number"));
                invLotDetails.setLotStatus(rs.getString("Lot_Current_Status"));
                invLotDetails.setWaferQtyIn(rs.getString("Wafer_Qty_In"));
                invLotDetails.setWaferQtyOut(rs.getString("Wafer_Qty_Out"));
                invLotDetails.setDieQtyIn(rs.getString("Die_Qty_In"));
                invLotDetails.setDieQtyOut(rs.getString("Die_Qty_Out"));
                invLotDetails.setDateIn(rs.getString("Date_In"));
                invLotDetails.setDateOut(rs.getString("Date_Out"));
                invLotDetails.setPoId(rs.getString("Po_No"));
                invLotDetails.setShopOrderNo(rs.getString("Shop_Order_No"));
                invLotDetails.setFabLotNumber(rs.getString("Fab_Lot_Number"));
                invLotDetails.setVendorLotNumber(rs.getString("Vendor_Lot_Number"));
                invLotDetails.setWaferIds(rs.getString("Wafer_Id"));
                invLotDetails.setLotCategory(rs.getString("Lot_Category"));
                invLotDetails.setDateCode(rs.getString("Lot_Date_Code"));
                invLotDetails.setComments(rs.getString("Lot_Comments"));
                invLotDetails.setAssemblyVendor(rs.getString("Assembly_Vendor"));
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Inventory Lot data from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return invLotDetails;
    }

    public static void saveTestBinInfo ( DBTransaction dbTransaction, LotDetails lotDetails ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;
        TestBinInfo testBinInfo = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall( "{CALL SP_SAVE_TEST_DATA_ENTRY_BINNING_INFO( ?, ?, ?, ?, ?, ? ) }" );
            int listSize = (lotDetails.getBinList() == null ? 0 : lotDetails.getBinList().size());

            for ( int i=0; i < listSize; i++ )
            {
                testBinInfo = (TestBinInfo)lotDetails.getBinList().get(i);

                if ( testBinInfo.getBinNo() != null && testBinInfo.getBinNo().trim().length() > 0 )
                {
                    cstmt.setString( 1, lotDetails.getProcessStepId() );
                    cstmt.setString( 2, lotDetails.getLocationId() );
                    cstmt.setString( 3, lotDetails.getVendorLotNumber() );
                    cstmt.setString( 4, lotDetails.getDieQtyOut() );
                    cstmt.setString( 5, testBinInfo.getBinNo() );
                    cstmt.setString( 6, testBinInfo.getBinQty() );

                    cstmt.executeUpdate();
                }

            }

            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save Test BINNING INFO", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore ) {}
            }
            if ( conn != null )
            {
                 try { conn.close(); } catch( SQLException ignore ) {}
            }
        }
    }

    public static void saveDieBankBinInfo ( DBTransaction dbTransaction, LotDetails lotDetails ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;
        TestBinInfo binInfo     = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall( "{CALL SP_SAVE_DIEBANK_DATA_ENTRY_BINNING_INFO( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            int listSize = (lotDetails.getBinList() == null ? 0 : lotDetails.getBinList().size());

            for ( int i=0; i < listSize; i++ )
            {
                int lotId;
                binInfo = (TestBinInfo)lotDetails.getBinList().get(i);

                if ( binInfo.getWaferId() != null && binInfo.getWaferId().trim().length() > 0 )
                {
                    try {
                        lotId = Integer.parseInt( lotDetails.getLotId() );
                    }
                    catch(Exception e)
                    {
                        lotId = 0;
                    }

                    cstmt.setString( 1, lotDetails.getProcessStepId() );
                    cstmt.setString( 2, lotDetails.getLocationId() );
                    cstmt.setString( 3, lotId > 0 ? lotDetails.getLotNumber() : lotDetails.getVendorLotNumber() );
                    cstmt.setString( 4, binInfo.getWaferId() );
                    cstmt.setString( 5, binInfo.getBinQty() );
                    cstmt.setString( 6, (binInfo.getBinNo() == null ? "Bin1" : binInfo.getBinNo() ) );
                    cstmt.setString( 7, (binInfo.getRejectedQty() == null ? "0" : binInfo.getRejectedQty()) );
                    cstmt.setString( 8, (binInfo.getYield() == null ? "0.0" : binInfo.getYield() )  );

                    cstmt.executeUpdate();
                }
            }

            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save Die Bank BINNING INFO", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore ) {}
            }
            if ( conn != null )
            {
                 try { conn.close(); } catch( SQLException ignore ) {}
            }
        }
    }


    public static ArrayList loadProcessList(DBTransaction dbTransaction, String processTypeName) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList ProcessList = null;

        try
        {
            ProcessList = new ArrayList();
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ CALL sp_all_load_process_types( ? ) }");
            pstmt.setString(1, processTypeName);

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                ProcessList.add(new LabelValueBean(rs.getString("PS_TYPE"), rs.getString("PS_TYPE_NAME")));
            }
        }
        catch (SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not load Process Types", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                } catch (SQLException igonore) {
                }
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException igonore) {
                }
            }

            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException igonore) {
                }
            }
        }
        return ProcessList;
    }

    public static ArrayList loadExternalVendorProcessList(DBTransaction dbTransaction, int vendorId) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList ProcessList = null;

        try
        {
            ProcessList = new ArrayList();
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT DISTINCT UPPER(PS_TYPE) AS PS_TYPE,UPPER(PS_TYPE)+'/'+UPPER(PS_TYPE_NAME) AS PS_TYPE_NAME,\n" +
                    "(CASE \n" +
                    "WHEN PS_TYPE_NAME = 'FAB' THEN 1 \n" +
                    "WHEN PS_TYPE_NAME = 'WAFER STORE' THEN 2\n" +
                    "WHEN PS_TYPE_NAME = 'BUMPING' THEN 3\n" +
                    "WHEN PS_TYPE_NAME = 'SORT' THEN 4\n" +
                    "WHEN PS_TYPE_NAME = 'DIE BANK' THEN 5 \n" +
                    "WHEN PS_TYPE_NAME = 'ASSEMBLY' THEN 6 \n" +
                    "WHEN PS_TYPE_NAME = 'TEST' THEN 7\n" +
                    "WHEN PS_TYPE_NAME = 'CLASS STORE' THEN 8\n" +
                    "WHEN PS_TYPE_NAME = 'MARK & PACK' THEN 9\n" +
                    "WHEN PS_TYPE_NAME = 'FINISHED GOODS' THEN 10\n" +
                    "END ) PS_TYPE_ID  FROM PROCESS_STEP ps, PROCESS_STEP_LOCATION_TABLE PSLT WHERE PS.PROCESSSTEP_ID = PSLT.PROCESSSTEP_ID\n" +
                    "AND PSLT.LOCATION_ID IN (SELECT LOCATION_Id FROM LOCATION WHERE VENDOR_ID = ? ) ORDER BY PS_TYPE_ID");
            pstmt.setInt(1, vendorId);

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                ProcessList.add(new LabelValueBean(rs.getString("PS_TYPE"), rs.getString("PS_TYPE")));
            }
        }
        catch (SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not load Process Types", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                } catch (SQLException igonore) {
                }
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException igonore) {
                }
            }

            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException igonore) {
                }
            }
        }
        return ProcessList;
    }

    public static ArrayList getLotInfoByPsType(String lotId, String psTypeName, String psType, int processstepId, int locationId, int newLotId ) throws Exception
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        LotDetails lotDetails =null;
        ArrayList lotsInfoList  = new ArrayList();
        String query = null;
        try
        {
            conn = DataSource.getConnection();

            query = "SELECT * FROM FN_GET_LOT_INFO( ?, ? , ? ) WHERE 1 = 1 ";

            if (processstepId > 0)
            {
                query = query + " AND PROCESSSTEP_ID = " + processstepId;
            }
            if (locationId > 0)
            {
                query = query + " AND LOCATION_ID = " + locationId;
            }
            if (newLotId > 0)
            {
                query = query + " AND LOT_ID = " + newLotId;
            }
            
            query = query + " ORDER BY PART_NUMBER";

            pstmt = conn.prepareStatement( query );
            pstmt.setString(1, lotId);
            pstmt.setString(2, psTypeName);
            pstmt.setString(3, psType);

            rs = pstmt.executeQuery();

           while (rs.next())
            {
                lotDetails = new LotDetails();

                lotDetails.setLotId(rs.getString("Lot_Id"));
                lotDetails.setProcessStepId(rs.getString("PROCESSSTEP_ID"));
                lotDetails.setLocationId(rs.getString("LOCATION_ID") );
                lotDetails.setPartNumber(rs.getString("Part_Number"));
                lotDetails.setLotNumber(rs.getString("Lot_Number"));
                lotDetails.setSplitLotOne(lotDetails.getLotNumber() + "A");
                lotDetails.setSplitLotTwo(lotDetails.getLotNumber() + "B");
                lotDetails.setLotStatus(rs.getString("Lot_Current_Status"));
                lotDetails.setWaferQtyIn(rs.getString("Wafer_Qty_In") );
                lotDetails.setDateIn(rs.getString("Date_In"));
                lotDetails.setDieQtyIn(rs.getString("Die_Qty_In"));
                lotDetails.setWaferIds(rs.getString("Wafer_Id"));
                lotDetails.setLotCategory(rs.getString("Lot_Category"));
                lotDetails.setDateCode(rs.getString("Lot_Date_Code"));
                lotDetails.setComments(rs.getString("Lot_Comments"));

                lotsInfoList.add( lotDetails );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Running and Hold Lot by PS Type data from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return lotsInfoList;
    }
    public static void saveLotSplitDetails( DBTransaction dbTransaction, String psType, ArrayList splitLotDetailsList, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails lotDetails = null;
        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call SP_CREATE_UPDATE_SPLIT_LOT( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? ) }");
            if ( splitLotDetailsList != null && splitLotDetailsList.size() > 0 )
            {
               for( int i=0; i < splitLotDetailsList.size(); i++)
                {
                    lotDetails = (LotDetails)splitLotDetailsList.get(i);

                    if ( lotDetails.isChecked() )
                    {                           
                        pstmt.setString(1, lotDetails.getProcessStepId());
                        pstmt.setString(2, lotDetails.getLocationId());
                        pstmt.setString(3, lotDetails.getLotId() );
                        pstmt.setString(4, psType );  //PS_TYPE
                        pstmt.setString(5, lotDetails.getSplitLotOne() );
                        pstmt.setString(6, lotDetails.getSplitLotTwo() );
                        pstmt.setString(7, lotDetails.getSplitLotOneQty() );
                        pstmt.setString(8, lotDetails.getSplitLotTwoQty() );
                        pstmt.setString(9, lotDetails.getSplitLotOneWaferIds() );
                        pstmt.setString(10, lotDetails.getSplitLotTwoWaferIds() );
                        pstmt.setString(11, lotDetails.getComments() );
                        pstmt.setString(12, userName );

                        pstmt.addBatch();
                    }
                }
            }
            pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Split Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }


    public static void saveDieBankReceiveInfo ( DBTransaction dbTransaction, ArrayList dieBankReceiveLotsList, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        InTransitLots dbReciveLots = null;

        if ( ( dieBankReceiveLotsList == null) || (dieBankReceiveLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call SP_CREATE_UPDATE_DIEBANK_RECEIVE_LOT( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < dieBankReceiveLotsList.size(); i++)
            {
                dbReciveLots = (InTransitLots) dieBankReceiveLotsList.get(i);
                
                if ( dbReciveLots.getCheckedWaferIdsAsString() != null )
                {
                    String waferArray[] = dbReciveLots.getCheckedWaferIdsAsString().split(";");

                    String uncheckedWaferIds = getUnCheckedWafers( waferArray, dbReciveLots.getWaferId().split(";") );

                    int remainingWaferQty = 0;
                    if ( uncheckedWaferIds != null && uncheckedWaferIds != "" )
                    {
                        remainingWaferQty    = uncheckedWaferIds.split(";").length;
                    }

                    pstmt.setInt(1, dbReciveLots.getDieBankprocessStepId() );
                    pstmt.setString(2, dbReciveLots.getVendorName() ); //FAB VENDOR NAME
                    pstmt.setString(3, dbReciveLots.getNextLocation() );
                    pstmt.setString(4, dbReciveLots.getFabLotNumber() ); //Fab Lot Number
                    pstmt.setString(5, dbReciveLots.getDieBankLotNo() ); //Lot Number
                    pstmt.setInt(6, dbReciveLots.getGrossDie() );
                    pstmt.setString( 7, userName);
                    pstmt.setString( 8, dbReciveLots.getProcessType() );
                    pstmt.setInt( 9, dbReciveLots.getLotId() );
                    pstmt.setString( 10, dbReciveLots.getShippedLotID() );
                    pstmt.setString( 11, dbReciveLots.getCheckedWaferIdsAsString() );
                    pstmt.setString( 12, uncheckedWaferIds);
                    pstmt.setInt(  13, remainingWaferQty);

                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Diebank receive Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void saveDieBankTransitInfo ( DBTransaction dbTransaction, ArrayList dieBankWipLotsList, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails dbTransitLotsDetils= null;
        String binQtyArray[]  = null;
        if ( ( dieBankWipLotsList == null) || (dieBankWipLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call SP_CREATE_UPDATE_DIEBANK_TRANSIT_LOT( ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < dieBankWipLotsList.size(); i++)
            {
                dbTransitLotsDetils = (LotDetails) dieBankWipLotsList.get(i);

                 pstmt.setString(1, dbTransitLotsDetils.getLotId() );
                 pstmt.setString(2, dbTransitLotsDetils.getShipDieQty() );
                 pstmt.setString(3, dbTransitLotsDetils.getAssemblyLotNumber() );
                 pstmt.setString(4, dbTransitLotsDetils.getNextLocation() );
                 pstmt.setString(5, dbTransitLotsDetils.getNextProcessType() );
                 pstmt.setString(6, userName );
                 pstmt.setString(7, dbTransitLotsDetils.getVendorName() );
                 pstmt.setInt(8, dbTransitLotsDetils.isAssyReleased() == true ? 1 : 0 );

                 pstmt.addBatch();
            }
            
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Diebank receive Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void updateDiebankReceiveLotBinInfo ( ArrayList dieBankReceiveLotsList, String callFrom ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        InTransitLots dbReciveLots = null;
        String grossDieArray[]  = null;
        if ( ( dieBankReceiveLotsList == null) || (dieBankReceiveLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("{ Call SP_CREATE_UPDATE_DIEBANK_RECEIVE_LOT_BIN_INFO( ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < dieBankReceiveLotsList.size(); i++)
            {
                dbReciveLots = (InTransitLots) dieBankReceiveLotsList.get(i);
                if (callFrom != null && "DieBankReceive".equalsIgnoreCase( callFrom ) )
                {
                     dbReciveLots.setChecked( true );   
                }

                if ( dbReciveLots.isChecked() && dbReciveLots.getCheckedWaferIdsAsString() != null )
                {
                    String waferArray[] = dbReciveLots.getCheckedWaferIdsAsString().split(";");

                    if ( dbReciveLots.getCheckedGDPWString() != null && dbReciveLots.getCheckedGDPWString().length() > 0 )
                    {
                        grossDieArray = dbReciveLots.getCheckedGDPWString().split(";");
                    }
                    else if ( LOCATION_TYPE_DIE_BANK.equalsIgnoreCase( dbReciveLots.getProcessType() ) )
                    {
                        grossDieArray = dbReciveLots.getGdpwString().split(";");
                    }

                    for ( int j = 0; j < waferArray.length; j++)
                    {                           
                        pstmt.setInt(1, dbReciveLots.getDieBankprocessStepId() );
                        pstmt.setString(2, dbReciveLots.getNextLocation() );
                        pstmt.setString(3, dbReciveLots.getDieBankLotNo() ); //Lot Number
                        pstmt.setString(4, waferArray[j] );
                        pstmt.setString(5, ( grossDieArray == null ? dbReciveLots.getGrossDiePerWafer() +"" : grossDieArray[j]  ) );
                        pstmt.setString(6, dbReciveLots.getProcessType() );
                        pstmt.setInt( 7, dbReciveLots.getLotId() );

                        pstmt.addBatch();
                    }
                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not uppdate Die Bank receive Lot Bin information", sqle);
        }

        finally
        {
             try
             {
                if (conn != null)
                {
                    if (success == true)
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore)
            {
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void updateDieBankTransitBinInfo (  ArrayList dieBankWaferBinInfoList ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt1 = null;
        boolean success         = false;
        LotDetails sortBinWaferLots= null;
        String binQtyArray[]  = null;
        if ( ( dieBankWaferBinInfoList == null) || (dieBankWaferBinInfoList.isEmpty() ) )
        {
            return;
        }

        try
        {
            conn = DataSource.getConnection();

            //WHEN SHIPPED FOR OTHER PROCESS
            pstmt1 = conn.prepareStatement("UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = BIN_1_BALANCE_QTY - ?, BIN_1_TRANSIT_QTY = ISNULL(BIN_1_TRANSIT_QTY, 0) + ? WHERE LOT_ID = ? AND WAFER_ID = ?");


            for (int i = 0; i < dieBankWaferBinInfoList.size(); i++)
            {
                sortBinWaferLots = (LotDetails) dieBankWaferBinInfoList.get(i);

                if ( sortBinWaferLots.getCheckedWaferIdsAsString() != null && sortBinWaferLots.isAssyReleased() == false)
                {
                    String waferArray[] = sortBinWaferLots.getCheckedWaferIdsAsString().split(";");
                    binQtyArray = sortBinWaferLots.getCheckedBinQtyAsString().split(";");

                    for ( int j = 0; j < waferArray.length; j++)
                    {
                        pstmt1.setInt(1, Integer.parseInt(binQtyArray[j] ));
                        pstmt1.setInt(2, Integer.parseInt(binQtyArray[j] ));
                        pstmt1.setInt(3, Integer.parseInt(sortBinWaferLots.getLotId() ) );
                        pstmt1.setInt(4, Integer.parseInt(waferArray[j]) );

                        pstmt1.execute();
                    }
                }
            }

            //pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not uppdate Sort bin release Lot information", sqle);
        }

        finally
        {
             try
             {
                if (conn != null)
                {
                    if (success == true)
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore)
            {
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    public static void updateDieBankBinInfo ( DBTransaction transaction, ArrayList dieBankWaferBinInfoList ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt1 = null;
        boolean success         = false;
        LotDetails sortBinWaferLots= null;
        String binQtyArray[]  = null;
        if ( ( dieBankWaferBinInfoList == null) || (dieBankWaferBinInfoList.isEmpty() ) )
        {
            return;
        }

        try
        {
            conn = DataSource.getConnection();

            //WHEN SHIPPED FOR OTHER PROCESS
            pstmt1 = conn.prepareStatement("UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = BIN_1_BALANCE_QTY - ? WHERE LOT_ID = ? AND WAFER_ID = ?");
            pstmt = conn.prepareStatement("UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = ISNULL(BIN_1_BALANCE_QTY,0) + ? WHERE LOT_ID = ? AND WAFER_ID = ?");

            for (int i = 0; i < dieBankWaferBinInfoList.size(); i++)
            {
                sortBinWaferLots = (LotDetails) dieBankWaferBinInfoList.get(i);

                if ( sortBinWaferLots.getCheckedWaferIdsAsString() != null )
                {
                    String waferArray[] = sortBinWaferLots.getCheckedWaferIdsAsString().split(";");
                    binQtyArray = sortBinWaferLots.getCheckedBinQtyAsString().split(";");

                    for ( int j = 0; j < waferArray.length; j++)
                    {
                        if ("PROD".equalsIgnoreCase(sortBinWaferLots.getTransferTo() ) )
                        {
                            pstmt.setInt(1, Integer.parseInt(binQtyArray[j] ));
                            pstmt.setInt(2, Integer.parseInt(sortBinWaferLots.getLotId() ) );
                            pstmt.setInt(3, Integer.parseInt(waferArray[j]) );

                            pstmt.execute();
                        }
                        else
                        {
                            pstmt1.setInt(1, Integer.parseInt(binQtyArray[j] ));
                            pstmt1.setInt(2, Integer.parseInt(sortBinWaferLots.getLotId() ) );
                            pstmt1.setInt(3, Integer.parseInt(waferArray[j]) );

                            pstmt1.execute();
                        }
                    }
                }
            }

            //pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not uppdate Sort bin release Lot information", sqle);
        }

        finally
        {
             try
             {
                if (conn != null)
                {
                    if (success == true)
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore)
            {
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt1 != null)
            {
                try
                {
                    pstmt1.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    
    public static void saveInventoryReceiveInfo ( DBTransaction dbTransaction, ArrayList inventroyReceiveLotsList, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        InTransitLots invReciveLots = null;

        if ( ( inventroyReceiveLotsList == null) || (inventroyReceiveLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_inventory_receive_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < inventroyReceiveLotsList.size(); i++)
            {
                invReciveLots = (InTransitLots) inventroyReceiveLotsList.get(i);

                if ( invReciveLots.isChecked() )
                {                      
                    pstmt.setString(1, invReciveLots.getProcessStepId() );
                    pstmt.setString(2, invReciveLots.getLotNumber() ); //Lot Number
                    pstmt.setString(3, invReciveLots.getFabLotNumber() );
                    pstmt.setString(4, invReciveLots.getShipQty() );  //QTY IN
                    pstmt.setString(5, invReciveLots.getNextLocation() ); //VENDOR NAME
                    pstmt.setString(6, invReciveLots.getNextProcessType() ); //RECEIVE PROCESS
                    pstmt.setString(7, userName );
                    pstmt.setString(8, invReciveLots.getShippedLotID() );
                    pstmt.setInt(9, invReciveLots.getLotId() );

                    if ("0".equalsIgnoreCase( invReciveLots.getDateCode() ) )
                    {
                        pstmt.setString(10, invReciveLots.getDateCode1() );
                    }
                    else
                    {
                        pstmt.setString(10, "-1".equals(invReciveLots.getDateCode() ) ? null : invReciveLots.getDateCode() );
                    }

                    pstmt.addBatch();

                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Inventory receive Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void saveInventoryReceiveInfoAllProcess ( DBTransaction dbTransaction, ArrayList inventroyReceiveLotsList, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        InTransitLots invReciveLots = null;

        if ( ( inventroyReceiveLotsList == null) || (inventroyReceiveLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_all_inventory_receive_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < inventroyReceiveLotsList.size(); i++)
            {
                invReciveLots = (InTransitLots) inventroyReceiveLotsList.get(i);

                if ( invReciveLots.isChecked() )
                {
                    String waferArray[] = invReciveLots.getCheckedWaferIdsAsString() != null ? invReciveLots.getCheckedWaferIdsAsString().split(";") : null;
                    String uncheckedWaferIds = null;
                    
                    if ( invReciveLots.getWaferId() != null )
                    {
                        uncheckedWaferIds = getUnCheckedWafers( waferArray, invReciveLots.getWaferId().split(";") );
                    }
                    int remainingWaferQty = 0;
                    if ( uncheckedWaferIds != null && !uncheckedWaferIds.equals("") )
                    {
                        remainingWaferQty    = uncheckedWaferIds.split(";").length;
                    }
                    pstmt.setString(1, invReciveLots.getProcessStepId() );
                    pstmt.setString(2, invReciveLots.getLotNumber() ); //Lot Number
                    pstmt.setString(3, invReciveLots.getFabLotNumber() );
                    pstmt.setString(4, invReciveLots.getShipQty() );
                    pstmt.setString(5, invReciveLots.getRecivedShipQty() );  //QTY IN
                    pstmt.setString(6, invReciveLots.getNextLocation() ); //TO VENDOR NAME
                    pstmt.setString(7, invReciveLots.getNextProcessType() ); //RECEIVE TO PROCESS
                    pstmt.setString(8, userName );
                    pstmt.setString(9, invReciveLots.getShippedLotID() );
                    pstmt.setInt(10, invReciveLots.getLotId() );
                    pstmt.setString( 11, invReciveLots.getCheckedWaferIdsAsString());
                    pstmt.setString( 12, uncheckedWaferIds);
                    pstmt.setInt(  13, remainingWaferQty);

                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            success = false;
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Inventory receive Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void saveRMAReceiveInfo ( DBTransaction dbTransaction, RMAReceiveForm rmaReceiveForm, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails rmaReceiveLots = null;

        ArrayList ramReceiveLotsList =  rmaReceiveForm.getRmaReceiveLotsList();

        if ( ( ramReceiveLotsList == null) || (ramReceiveLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ CALL SP_CREATE_UPDATE_RMA_RECEIVE_LOT( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");

            for (int i = 0; i < ramReceiveLotsList.size(); i++)
            {
                rmaReceiveLots = (LotDetails) ramReceiveLotsList.get(i);

                if ( rmaReceiveLots.isChecked() )
                {
                    pstmt.setInt(1, rmaReceiveForm.getCustomerId() );
                    pstmt.setInt(2, rmaReceiveForm.getRmaReceiveId() );
                    pstmt.setString(3, rmaReceiveForm.getRmaReceiveNo() );
                    pstmt.setInt(4, rmaReceiveLots.getRmaReceiveLotId() );
                    pstmt.setString(5, rmaReceiveLots.getProcessStepId() );
                    pstmt.setString(6, rmaReceiveLots.getLocationId() );
                    pstmt.setString(7, rmaReceiveLots.getLotNumber() );
                    pstmt.setString(8, rmaReceiveLots.getFabLotNumber() );
                    pstmt.setString(9, rmaReceiveLots.getDieQtyIn() );
                    pstmt.setString(10, rmaReceiveLots.getDateIn() );
                    pstmt.setString(11, rmaReceiveLots.getComments() );
                    pstmt.setInt(12, rmaReceiveLots.isClosed() ? 0 : 1 );
                    pstmt.setString(13, userName );
                    if ("0".equalsIgnoreCase( rmaReceiveLots.getDateCode() ) )
                    {
                        pstmt.setString(14, rmaReceiveLots.getDateCode1() );
                    }
                    else
                    {
                        pstmt.setString(14, "-1".equals(rmaReceiveLots.getDateCode() ) ? null : rmaReceiveLots.getDateCode() );
                    }

                    pstmt.addBatch();

                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save RMA Receive Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void saveRMAReceiveWIPInfo ( DBTransaction dbTransaction, ArrayList ramReceiveLotsList, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails ramWIPLots = null;

        if ( ( ramReceiveLotsList == null) || (ramReceiveLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ CALL SP_CREATE_UPDATE_RMA_RECEIVE_WIP_LOT( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");

            for (int i = 0; i < ramReceiveLotsList.size(); i++)
            {
                ramWIPLots = (LotDetails) ramReceiveLotsList.get(i);

                if ( ramWIPLots.isChecked() )
                {
                    pstmt.setInt(1, ramWIPLots.getRmaReceiveId() );
                    pstmt.setInt(2, ramWIPLots.getRmaReceiveLotId() );
                    pstmt.setString(3, ramWIPLots.getProcessStepId() );
                    pstmt.setString(4, ramWIPLots.getLocationId() );
                    pstmt.setString(5, ramWIPLots.getLotNumber() );
                    pstmt.setString(6, ramWIPLots.getDateCode() );
                    pstmt.setString(7, ramWIPLots.getShipDieQty() );
                    pstmt.setString(8, ramWIPLots.getDateIn() );
                    pstmt.setString(9, ramWIPLots.getNextLocation() );
                    pstmt.setString(10, ramWIPLots.getNextProcessType() );
                    pstmt.setString(11, ramWIPLots.getComments() );
                    pstmt.setString(12, userName );

                    pstmt.addBatch();

                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save RMA Receive Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {                               
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static String getRmaNumber( int rmaLotId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        String rmaNo = null;
        if ( !(rmaLotId > 0) )
        {
            return null;
        }
         try
        {
           conn = DataSource.getConnection();
           pstmt = conn.prepareStatement("SELECT RMA_RECEIVE_NUMBER FROM RMA_RECEIVE_TABLE WHERE RMA_RECEIVE_ID = ? ");

            pstmt.setInt(1, rmaLotId);
            rs = pstmt.executeQuery();

            if (rs.next())
            {
                rmaNo = rs.getString("RMA_RECEIVE_NUMBER");
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get RMA RECEIVE Number By Rma ID", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return rmaNo;
    }
    public static void saveInventoryTransitInfo ( DBTransaction dbTransaction, InventoryTransitForm inventroyTransitForm, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails invTransitLots = null;

        ArrayList inventroyTransitLotsList =  inventroyTransitForm.getWipLotsList();

        if ( ( inventroyTransitLotsList == null) || (inventroyTransitLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_inventory_transit_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}");

            for (int i = 0; i < inventroyTransitLotsList.size(); i++)
            {
                invTransitLots = (LotDetails) inventroyTransitLotsList.get(i);

                if ( invTransitLots.isChecked() )
                {
                    pstmt.setString(1, invTransitLots.getLotId() );
                    pstmt.setString(2, invTransitLots.getShipDieQty() ); //Ship QTY
                    pstmt.setString(3, invTransitLots.getNextLocation() );
                    pstmt.setString(4, invTransitLots.getNextProcessType() );
                    pstmt.setString(5, userName );
                    pstmt.setString(6, invTransitLots.getPartNumber() );
                    pstmt.setString(7, invTransitLots.getLotNumber() );
                    pstmt.setString(8, invTransitLots.getFabLotNumber() );
                    pstmt.setString(9, invTransitLots.getVendorName() );

                    if ("0".equalsIgnoreCase( invTransitLots.getDateCode() ) )
                    {
                        pstmt.setString(10, invTransitLots.getDateCode1() );
                    }
                    else
                    {
                        pstmt.setString(10, "-1".equals(invTransitLots.getDateCode() ) ? null : invTransitLots.getDateCode() );
                    }
                    if ( invTransitLots.isLotHold() )
                    {
                        pstmt.setString(11, LOT_STATUS_HOLD );
                    }
                    else
                    {
                       pstmt.setString(11, LOT_STATUS_RUNNING );
                    }
                    pstmt.setString(12, invTransitLots.getComments() );
                    pstmt.setString(13, invTransitLots.getBulkDieQty() );
                    pstmt.setString(14, inventroyTransitForm.getProcessType() );
                    pstmt.addBatch();

                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Inventory Transit Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    public static void saveInventoryTransitInfoAllProcess ( DBTransaction dbTransaction, InventoryTransitForm inventroyTransitForm, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails invTransitLots = null;

        ArrayList inventroyTransitLotsList =  inventroyTransitForm.getWipLotsList();

        if ( ( inventroyTransitLotsList == null) || (inventroyTransitLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call sp_create_update_all_inventory_transit_lot( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?,?,?,? )}");

            for (int i = 0; i < inventroyTransitLotsList.size(); i++)
            {
                invTransitLots = (LotDetails) inventroyTransitLotsList.get(i);

                if ( invTransitLots.isChecked() )
                {
                    String waferArray[] = invTransitLots.getCheckedWaferIdsAsString() != null ? invTransitLots.getCheckedWaferIdsAsString().split(";") : null;
                    String uncheckedWaferIds = null;

                    if ( invTransitLots.getWaferIds() != null )
                    {
                        uncheckedWaferIds = getUnCheckedWafers( waferArray, invTransitLots.getWaferIds().split(";") );
                    }
                    int remainingWaferQty = 0;
                    if ( uncheckedWaferIds != null && !uncheckedWaferIds.equals("") )
                    {
                        remainingWaferQty    = uncheckedWaferIds.split(";").length;
                    }
                    
                    pstmt.setString(1, invTransitLots.getLotId() );
                    pstmt.setString(2, invTransitLots.getShipQty() ); //Ship Wafer QTY
                    pstmt.setString(3, invTransitLots.getShipDieQty() ); //Ship Die QTY
                    pstmt.setString(4, invTransitLots.getNextLocation() );
                    pstmt.setString(5, invTransitLots.getNextProcessType() );
                    pstmt.setString(6, userName );
                    pstmt.setString(7, invTransitLots.getPartNumber() );
                    pstmt.setString(8, invTransitLots.getLotNumber() );
                    pstmt.setString(9, invTransitLots.getFabLotNumber() );
                    pstmt.setString(10, invTransitLots.getVendorName() );
                    pstmt.setString( 11, invTransitLots.getCheckedWaferIdsAsString());
                    pstmt.setString( 12, uncheckedWaferIds);
                    pstmt.setInt(  13, remainingWaferQty);

                    if ( invTransitLots.isLotHold() )
                    {
                        pstmt.setString(14, LOT_STATUS_HOLD );
                    }
                    else
                    {
                       pstmt.setString(14, LOT_STATUS_RUNNING );
                    }
                    pstmt.setString(15, invTransitLots.getComments() );
                    pstmt.setString(16, inventroyTransitForm.getProcessType() );
                    pstmt.setString(17, invTransitLots.getSplitLotOne() );
                    pstmt.setString(18, invTransitLots.getSplitLotTwo() );
                    pstmt.setString(19, invTransitLots.getSplitLotOneQty() );
                    pstmt.setString(20, invTransitLots.getSplitLotTwoQty() );
                    
                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save Inventory Transit Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void saveInventoryProgramLotsInfo( DBTransaction dbTransaction, InventoryTransitForm inventroyTransitForm, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails invTransitLots = null;

        ArrayList inventroyTransitLotsList =  inventroyTransitForm.getWipLotsList();

        if ( ( inventroyTransitLotsList == null) || (inventroyTransitLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call SP_CREATE_UPDATE_PROG_INVENTORY_LOT( ?, ?, ?, ?, ?, ? ) } ");

            for (int i = 0; i < inventroyTransitLotsList.size(); i++)
            {
                invTransitLots = (LotDetails) inventroyTransitLotsList.get(i);

                if ( invTransitLots.isChecked() )
                {
                    pstmt.setString(1, invTransitLots.getLotId() );
                    pstmt.setString(2, invTransitLots.getShipDieQty() ); //Ship Die Qty
                    pstmt.setInt(3, invTransitLots.getScrapDieQty() ); //Scrap Die QTY
                    pstmt.setString(4, invTransitLots.getNextLocation() );
                    pstmt.setString(5, userName );
                    pstmt.setInt(6, invTransitLots.getToProcessStepId() );

                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save FG Inventory Program Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }


    public static void saveTransferedLotDetails ( DBTransaction dbTransaction, ArrayList transferLotsList, String userName, String processType ) throws Exception
    {
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails invLots = null;

        if ( ( transferLotsList == null) || (transferLotsList.isEmpty() ) )
        {
            return;
        }
    	try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}


            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_TRANSFER_MATERAIL_LOT (?, ?, ?, ?, ?, ?, ?, ?,? ) }" );

            for (int i = 0; i < transferLotsList.size(); i++)
            {
                invLots = (LotDetails) transferLotsList.get(i);

                if ( invLots.isChecked() )
                {
                    String waferArray[] = invLots.getCheckedWaferIdsAsString() != null ? invLots.getCheckedWaferIdsAsString().split(";") : null;
                    String uncheckedWaferIds = null;

                    if ( invLots.getWaferIds() != null )
                    {
                        uncheckedWaferIds = getUnCheckedWafers( waferArray, invLots.getWaferIds().split(";") );
                    }

                    pstmt.setString(1, invLots.getLotId() );
                    pstmt.setString(2, invLots.getShipDieQty() ); //Transfer QTY
                    pstmt.setString(3, invLots.getCheckedWaferIdsAsString() );
                    pstmt.setString(4, uncheckedWaferIds );
                    pstmt.setString(5, invLots.getScrapComments() );
                    pstmt.setString(6, processType );
                    pstmt.setString(7, invLots.getTransferFrom() );
                    pstmt.setString(8, invLots.getTransferTo() );
                    pstmt.setString(9, userName );


                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;

        }
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Tranfer material SetUp from database", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}

			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
    }
    public static ArrayList<LotDetails> getAllRunningLots(String processType, int processstepId, int locationId, int lotId, boolean aviodTrFgparts) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<LotDetails> runningWipLotsList = new ArrayList<LotDetails>();

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("{ Call SP_GET_ALL_RUNNING_LOTS ( ?, ?, ?, ?, ? ) } ");

            pstmt.setString(1, processType );
            pstmt.setInt(2, processstepId );
            pstmt.setInt(3, locationId );
            pstmt.setInt(4, lotId );
            pstmt.setBoolean(5, aviodTrFgparts);

            rs = pstmt.executeQuery();

            LotDetails lotDetails;
            WorkWeek wk = WorkWeek.getCurrentWorkWeekInstance();
            int currentwk = wk.getCurrentWWNumber();
            String yearAsDigit  = (wk.getWorkWeekYear()+"").substring(2);

            while (rs.next())
            {
                lotDetails = new LotDetails();

                lotDetails.setLotId( rs.getString("LOT_ID") );
                lotDetails.setProcessStepId(rs.getString("PROCESSSTEP_ID"));
                lotDetails.setLocationId(rs.getString("LOCATION_ID"));
                lotDetails.setPartNumber( rs.getString("PART_NUMBER") );
                lotDetails.setLotNumber(rs.getString("LOT_NUMBER"));
                lotDetails.setFabLotNumber(rs.getString("FAB_LOT_NUMBER"));
                lotDetails.setDieQtyIn( rs.getString("QTY_IN"));
                lotDetails.setWaferQtyIn( rs.getString("WAFER_QTY_IN"));
                lotDetails.setDateIn( rs.getString("DATE_IN"));
                lotDetails.setVendorName( rs.getString("VENDOR_NAME") );
                lotDetails.setNextLocation( rs.getString("NEXT_VENDOR") );
                lotDetails.setNextProcessType(rs.getString("NEXT_PROCESS") );
                
                if ( rs.getString("DATE_CODE") != null && !"".equals(rs.getString("DATE_CODE") ) )
                {
                    lotDetails.setDateCode(rs.getString("DATE_CODE") );
                }
                else
                {
                    if ( processType.equalsIgnoreCase(LOCATION_TYPE_ASSEMBLY) )
                    {
                        lotDetails.setDateCode( null);
                    }
                    else
                    {
                        lotDetails.setDateCode( yearAsDigit + (currentwk < 10 ? "0"+currentwk : currentwk+""));
                    }
                }
                               
                if ("hold".equalsIgnoreCase( rs.getString("LOT_STATUS") ) )
                {
                    lotDetails.setLotHold( true );
                }
                lotDetails.setComments( rs.getString("LOT_COMMENTS") );
                
                runningWipLotsList.add( lotDetails );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get all running lots", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return runningWipLotsList;
    }

    public static ArrayList<LotDetails> getAllRunningHoldLots(String processType, int processstepId, int locationId, int lotId) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<LotDetails> runningWipLotsList = new ArrayList<LotDetails>();

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("{ Call SP_GET_ALL_RUNNING_AND_HOLD_LOTS ( ?, ?, ?, ? ) } ");

            pstmt.setString(1, processType );
            pstmt.setInt(2, processstepId );
            pstmt.setInt(3, locationId );
            pstmt.setInt(4, lotId );


            rs = pstmt.executeQuery();

            LotDetails lotDetails;

            while (rs.next())
            {
                lotDetails = new LotDetails();

                lotDetails.setLotId( rs.getString("LOT_ID") );
                lotDetails.setProcessStepId(rs.getString("PROCESSSTEP_ID"));
                lotDetails.setLocationId(rs.getString("LOCATION_ID"));
                lotDetails.setPartNumber( rs.getString("PART_NUMBER") );
                lotDetails.setLotNumber(rs.getString("LOT_NUMBER"));
                lotDetails.setFabLotNumber(rs.getString("FAB_LOT_NUMBER"));
                lotDetails.setDieQtyIn( rs.getString("QTY_IN"));
                lotDetails.setShipDieQty( rs.getString("QTY_IN"));
                lotDetails.setWaferQtyIn( rs.getString("WAFER_QTY_IN"));
                lotDetails.setShipQty( rs.getString("WAFER_QTY_IN"));
                lotDetails.setDateIn( rs.getString("DATE_IN"));
                lotDetails.setVendorName( rs.getString("VENDOR_NAME") );
                lotDetails.setNextLocation( rs.getString("NEXT_VENDOR") );
                lotDetails.setNextProcessType(rs.getString("NEXT_PROCESS") );
                lotDetails.setWaferIds(rs.getString("WAFER_ID") );
                
                if ("hold".equalsIgnoreCase( rs.getString("LOT_STATUS") ) )
                {
                    lotDetails.setLotHold( true );
                }
                lotDetails.setComments( rs.getString("LOT_COMMENTS") );

                runningWipLotsList.add( lotDetails );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get all running lots", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return runningWipLotsList;
    }

    public static ArrayList<LotDetails> getAllRunningLotsByProcess( String processType, int processstepId, int locationId, int lotId ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<LotDetails> runningWipLotsList = new ArrayList<LotDetails>();

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("{ Call SP_AVAILABLE_LOTS_FOR_TRANSFER ( ?, ?, ?, ? ) } ");

            pstmt.setString( 1, processType );
            pstmt.setInt( 2, processstepId );
            pstmt.setInt( 3, locationId );
            pstmt.setInt( 4, lotId );

            rs = pstmt.executeQuery();

            LotDetails lotDetails;

            while (rs.next())
            {
                lotDetails = new LotDetails();

                lotDetails.setLotId( rs.getString( "LOT_ID" ) );
                lotDetails.setProcessStepId( rs.getString( "PROCESSSTEP_ID" ) );
                lotDetails.setLocationId( rs.getString( "LOCATION_ID" ) );
                lotDetails.setPartNumber( rs.getString( "PART_NUMBER" ) );
                lotDetails.setLotNumber( rs.getString( "LOT_NUMBER" ) );
                lotDetails.setFabLotNumber( rs.getString( "FAB_LOT_NUMBER" ) );
                lotDetails.setWaferQtyIn( rs.getString( "WAFER_QTY_IN" ) );
                lotDetails.setDieQtyIn( rs.getString( "QTY_IN" ) );                
                lotDetails.setDateIn( rs.getString( "DATE_IN" ) );
                lotDetails.setVendorName( rs.getString( "VENDOR_NAME" ) );
                lotDetails.setDateCode( rs.getString( "DATE_CODE" ) );
                lotDetails.setWaferIds( rs.getString( "WAFER_IDS" ) );
                if ("hold".equalsIgnoreCase( rs.getString( "LOT_STATUS" ) ) )
                {
                    lotDetails.setLotHold( true );
                }
                lotDetails.setComments( rs.getString("LOT_COMMENTS") );
                
                runningWipLotsList.add( lotDetails );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get all running lots", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return runningWipLotsList;
    }
    public static ArrayList<LotDetails> loadAllDieBankWipLots(int processstepId, int locationId, int lotId, boolean releaseFlag ) throws ScalarException
    {
        return loadAllDieBankWipLots( processstepId, locationId, lotId, releaseFlag, true );

    }
    public static ArrayList<LotDetails> loadAllDieBankWipLots(int processstepId, int locationId, int lotId, boolean releaseFlag, boolean showReleaseLots ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<LotDetails> runningWipLotsList = new ArrayList<LotDetails>();

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("{ Call SP_GET_ALL_DIEBANK_RUNNING_LOTS( ?, ?, ?, ?, ? ) } ");

            pstmt.setInt(1, processstepId );
            pstmt.setInt(2, locationId );
            pstmt.setInt(3, lotId);
            pstmt.setString(4, releaseFlag ? "TRUE" : "FALSE" );
            pstmt.setString(5, showReleaseLots ? "TRUE" : "FALSE" );

            rs = pstmt.executeQuery();

            LotDetails lotDetails;

            while (rs.next())
            {
                lotDetails = new LotDetails();

                lotDetails.setLotId( rs.getString("LOT_ID") );
                lotDetails.setProcessStepId( rs.getString("PROCESSSTEP_ID") );
                lotDetails.setPartNumber( rs.getString("PART_NUMBER") );
                lotDetails.setProcessType( rs.getString("PS_TYPE") );
                lotDetails.setLotNumber(rs.getString("LOT_NUMBER"));
                lotDetails.setFabLotNumber(rs.getString("FAB_LOT_NUMBER"));
                lotDetails.setDieQtyIn( rs.getString("QTY_IN") );
                lotDetails.setVendorName( rs.getString("VENDOR_NAME") );
                lotDetails.setWaferIds( rs.getString("WAFER_IDS") );
                lotDetails.setBinQtyAsString( rs.getString("BIN_QTY_STRING") );
                lotDetails.setAvailableBinQtyAsStr( rs.getString("BIN_QTY_STRING") );
                lotDetails.setScrapQtyAsString(rs.getString("BIN_QTY_STRING") );
                lotDetails.setNextProcessType( rs.getString("NEXT_PROCESS") );
                lotDetails.setNextLocation( rs.getString("NEXT_VENDOR") );
                lotDetails.setAssyReleased( rs.getInt("RELEASE_FLAG") == 0 ? false : true );
                
                runningWipLotsList.add( lotDetails );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get all running lots", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return runningWipLotsList;
    }

     public static ArrayList getAllNonValueInventoryLots(String processType, int processstepId, int locationId, int lotId ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList nonValueInvLotsList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("{ Call SP_GET_ALL_NONVALUE_INVENTORY_LOTS ( ?, ?, ?, ?) } ");

            pstmt.setString(1, processType );
            pstmt.setInt(2, processstepId );
            pstmt.setInt(3, locationId );
            pstmt.setInt(4, lotId );

            rs = pstmt.executeQuery();

            LotDetails lotDetails;

            while (rs.next())
            {
                lotDetails = new LotDetails();

                lotDetails.setLotId( rs.getString("LOT_ID") );
                lotDetails.setProcessStepId(rs.getString("PROCESSSTEP_ID"));
                lotDetails.setLocationId(rs.getString("LOCATION_ID"));
                lotDetails.setPartNumber( rs.getString("PART_NUMBER") );
                lotDetails.setLotNumber(rs.getString("LOT_NUMBER"));
                lotDetails.setFabLotNumber(rs.getString("FAB_LOT_NUMBER"));
                lotDetails.setScrapDieQty( rs.getInt("QTY_IN"));
                lotDetails.setDateIn( rs.getString("DATE_IN"));
                lotDetails.setVendorName( rs.getString("VENDOR_NAME") );
                lotDetails.setDateCode(rs.getString("DATE_CODE") );
                lotDetails.setScrapComments( rs.getString("LOT_COMMENTS") );
                lotDetails.setWaferIds( rs.getString("WAFER_IDS") );
                lotDetails.setDateIn( rs.getString("DATE_IN"));
                nonValueInvLotsList.add( lotDetails );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get all running lots", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return nonValueInvLotsList;
    }

    public static int isGenerateLotNumber() throws Exception
    {
        int generateLotFlag     = 0;
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT Generate_LotNo FROM Lot_Number_Rule_Table");
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {

               generateLotFlag = rset.getInt("Generate_LotNo") ;

            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load Getgenerate Lot Number status from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return generateLotFlag;
    }

    public static String getUnCheckedWafers( String[] checkedWafers, String[] totalwafers ) throws Exception
    {
        String wafers            = "";
        boolean     flag         = false;
        
        for ( int i= 0; i < totalwafers.length; i++ )
        {
            for( int j=0; j < checkedWafers.length; j++ )
            {
                if ( totalwafers[i] != null && checkedWafers[j] != null )
                {
                    if ( totalwafers[i].toString().equals(checkedWafers[j].toString()) )
                    {
                        flag = false;
                        break;
                    }
                    else
                    {
                        flag = true;
                    }
               }
            }
            if ( flag )
            {
                wafers   = (wafers != "" ? wafers + ";" + totalwafers[i].toString() : totalwafers[i].toString() );
            }
        }

       return wafers;
    }

    public static String getLastGeneratedAssemblyLotNo( String lotNumber ) throws Exception
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String assyLotNo = null;

        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT TOP 1 LOT_NUMBER FROM LOT_TABLE WHERE LOT_NUMBER LIKE ? Order by Lot_NUmber DESC");
            pstmt.setString(1, lotNumber +"%");

            rs = pstmt.executeQuery();

            if (rs.next())
            {
                assyLotNo = rs.getString("Lot_Number");
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Last Generated Assembly LotNo from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return assyLotNo;
    }

    public static int getNoOfWafersPerLot( int fabProcessStepId ) throws Exception
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        int noOfWafers = 0;

        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT NO_OF_WAFERS_PER_LOT FROM ASSEMBLY_RELEASE_LOT_SIZE_MAX_QTY_TABLE WHERE PROCESSSTEP_ID = ? ");
            pstmt.setInt(1, fabProcessStepId);

            rs = pstmt.executeQuery();

            if (rs.next())
            {
                noOfWafers = rs.getInt("NO_OF_WAFERS_PER_LOT");
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get no of wafers per lot for Assembly Lot No from database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return noOfWafers;
    }

    public static ArrayList getAllRMAReceivedLotsListByCustomer(int customerId, int locationId) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList rmaLotsList = null;
        String query         = null;
        
        try
        {
            rmaLotsList = new ArrayList();

            conn = DataSource.getConnection();
            query = "SELECT DISTINCT RRT.RMA_RECEIVE_ID, RMA_RECEIVE_NUMBER FROM RMA_RECEIVE_TABLE RRT, RMA_RECEIVE_LOT_TABLE RRLT WHERE RRT.RMA_RECEIVE_ID = RRLT.RMA_RECEIVE_ID AND RMA_STATUS <> 'Close'";

            if ( customerId > 0 )
            {
                query = query + " AND CUSTOMER_ID = " + customerId ;
            }
            if ( locationId > 0 )
            {
                query = query + " AND LOCATION_ID = " + locationId ;
            }

            query = query + " ORDER BY RMA_RECEIVE_NUMBER";
            //System.out.println("query = " + query);
            pstmt = conn.prepareStatement( query );
            //pstmt = conn.prepareStatement("SELECT RMA_RECEIVE_ID, RMA_RECEIVE_NUMBER FROM RMA_RECEIVE_TABLE RRT, RMA_RECEIVE_TABLE RRLT WHERE RRT.RMA_RECEIVE_ID = RRLT.RMA_RECEIVE_ID AND CUSTOMER_ID = ? AND RMA_STATUS <> 'Close' ");

            //pstmt.setInt(1, customerId);
            rs = pstmt.executeQuery();

            while (rs.next())
            {
                rmaLotsList.add(new LabelValueBean(rs.getString("RMA_RECEIVE_NUMBER"), rs.getInt("RMA_RECEIVE_ID")+"") ) ;
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not load RmaNo's For selected Customer ", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return rmaLotsList;
    }

    public static ArrayList getAllRMAReceivedLots(int ramReceiveId, int processstepId, int locationId, int newLotId ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList rmaLotsList = new ArrayList();
        LotDetails lotDetails = null;
        String query = null;
        try
        {
            conn = DataSource.getConnection();

            query = "SELECT RRT.RMA_RECEIVE_ID, RMA_RECEIVE_LOT_ID, RMA_RECEIVE_NUMBER, PROCESSSTEP_ID, (SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID =RRLT.PROCESSSTEP_ID) AS PART_NUMBER , LOCATION_ID, (SELECT L_VENDOR_NAME FROM LOCATION WHERE LOCATION_ID = RRLT.LOCATION_ID) AS VENDOR_NAME, LOT_NUMBER, DATE_CODE, QTY_IN , CONVERT(VARCHAR , DATE_IN , 101) AS DATE_IN, COMMENTS  FROM RMA_RECEIVE_TABLE RRT, RMA_RECEIVE_LOT_TABLE RRLT WHERE RRT.RMA_RECEIVE_ID = RRLT.RMA_RECEIVE_ID AND STATUS = 'Receive' ";

            if ( ramReceiveId > 0)
            {
                query = query + "  AND RRT.RMA_RECEIVE_ID = " + ramReceiveId;
            }
            if (processstepId > 0)
            {
                query = query + "  AND PROCESSSTEP_ID = " + processstepId;
            }
            if (locationId > 0)
            {
                query = query + " AND LOCATION_ID = " + locationId;
            }
            if (newLotId > 0)
            {
                query = query + " AND RMA_RECEIVE_LOT_ID = " + newLotId;
            }

            query = query + " ORDER BY LOT_NUMBER";

            //System.out.println("query = " + query);
            
            pstmt = conn.prepareStatement( query );

            //pstmt = conn.prepareStatement("SELECT RMA_RECEIVE_LOT_ID, RMA_RECEIVE_NUMBER, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, QTY_IN , CONVERT(VARCHAR , DATE_IN , 101) AS DATE_IN, COMMENTS  FROM RMA_RECEIVE_TABLE RMT, RMA_RECEIVE_LOT_TABLE RMLT WHERE RMT.RMA_RECEIVE_ID = RMLT.RMA_RECEIVE_ID AND RMT.RMA_RECEIVE_ID = ? AND STATUS <> 'Close' ");

            //pstmt.setInt(1, ramReceiveId);
            rs = pstmt.executeQuery();

            while (rs.next())
            {
                lotDetails = new LotDetails();

                //lotDetails.setChecked( true );
                lotDetails.setRmaReceiveId( rs.getInt("RMA_RECEIVE_ID") );
                lotDetails.setRmaReceiveLotId( rs.getInt("RMA_RECEIVE_LOT_ID") );
                lotDetails.setRmaReceiveNo( rs.getString("RMA_RECEIVE_NUMBER") );
                lotDetails.setProcessStepId( rs.getString("PROCESSSTEP_ID") );
                lotDetails.setPartNumber( rs.getString("PART_NUMBER") );
                lotDetails.setLocationId( rs.getString("LOCATION_ID") );
                lotDetails.setVendorName( rs.getString("VENDOR_NAME") );
                lotDetails.setLotNumber( rs.getString("LOT_NUMBER") );
                lotDetails.setDateCode( rs.getString("DATE_CODE") );
                lotDetails.setDieQtyIn( rs.getString("QTY_IN") );
                lotDetails.setDateIn( rs.getString("DATE_IN") );
                lotDetails.setComments( rs.getString("COMMENTS") );
                
                rmaLotsList.add( lotDetails );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not load RmaNo lot deatils Customer ", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return rmaLotsList;
    }

    public static void moveTransitLotsToWIP (DBTransaction dbTransaction, ArrayList transitLotsList, String nextProcess ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        int listSize            = 0;
        InTransitLots inTransitLots = null;

        Calendar cal = Calendar.getInstance();
        java.util.Date todayDate = cal.getTime();
        String currentDate = StringUtils.formatDateToString( todayDate, "MM/dd/yyyy" );

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall ( "{ CALL SP_MOVE_TRANSIT_LOTS_TO_NEXT_PROCESS( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

            if ( transitLotsList != null && transitLotsList.size() > 0 )
            {
                listSize = transitLotsList.size();
                for ( int i= 0; i< listSize; i++)
                {
                    inTransitLots = (InTransitLots)transitLotsList.get(i);

                    if( inTransitLots.isChecked() == true )
                    {                           
                        cstmt.setString (1, inTransitLots.getPartNumber() );
                        cstmt.setString (2, inTransitLots.getLotNumber() );
                        cstmt.setString( 3,  inTransitLots.getFabLotNumber() );
                        if ( LOCATION_TYPE_DIE_BANK.equalsIgnoreCase( nextProcess ) )
                        {
                            cstmt.setString (4, inTransitLots.getGrossDie()+"" ); //DIE QTY
                            cstmt.setString (5, inTransitLots.getShipQty() );     //WAFER_QTY
                        }
                        else
                        {
                            cstmt.setString (4, inTransitLots.getShipQty() );
                            cstmt.setString (5, inTransitLots.getShipQty() );
                        }
                        cstmt.setString (6, currentDate );
                        cstmt.setString (7, nextProcess );
                        cstmt.setString (8, inTransitLots.getMoveTo() ); //Next Location Vendor
                        cstmt.setString( 9, LOT_STATUS_RUNNING );
                        cstmt.setString( 10, inTransitLots.getWaferId() );

                        cstmt.addBatch();
                   }
                }
                cstmt.executeBatch();
                success = true;
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not move transit lots Information into database", sqle);
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
						}
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) { ignore.printStackTrace(); }
            }

            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
        }
	}

    public static AutoGrowingArrayList getDataEntryLots( DBTransaction dbTransaction, String psType, String vendorName ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        AutoGrowingArrayList dataEntryLotsList = new AutoGrowingArrayList(DataEntryLot.class);
        DataEntryLot dataEntryLot = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall ( "{CALL SP_LOAD_ALL_DATAENTRY_LOTS( ?,? )}" );
            cstmt.setString( 1, psType );
            cstmt.setString( 2, vendorName );

            rset = cstmt.executeQuery();
            while ( rset.next() )
            {
                dataEntryLot = new DataEntryLot();
                dataEntryLot.setLotId( rset.getInt("LOT_ID") );
                dataEntryLot.setLotNumber( rset.getString ( "LOT_NUMBER" ) );
                dataEntryLot.setFabLotNumber( rset.getString ( "FAB_LOT_NUMBER" ) );
                dataEntryLot.setPartNumber( rset.getString ( "PART_NUMBER" ) );
                dataEntryLot.setPsType( rset.getString ( "PS_TYPE" ) );
                dataEntryLot.setVendorName( rset.getString ( "VENDOR_NAME" ) );
                dataEntryLot.setCurrentQty( rset.getInt( "QTY_IN" ) );
                dataEntryLot.setDateIn( rset.getString ( "DATE_IN" ) );

                dataEntryLotsList.add( dataEntryLot );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not get Data Entry Lots", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return dataEntryLotsList;
    }

    public static boolean closeDataEntryLots( DBTransaction dbTransaction, ArrayList dataEntryLotsList, User user ) throws ScalarException
    {
        boolean success = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        DataEntryLot dataEntryLot = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' , USER_NAME = ? WHERE LOT_ID = ?" );
            int dataEntrylotsListSize = (dataEntryLotsList == null) ? 0 : dataEntryLotsList.size();

            for ( int i=0; i < dataEntrylotsListSize; i++ )
            {
                dataEntryLot = (DataEntryLot)dataEntryLotsList.get ( i );
                                
                pstmt.setString ( 1, user.getUserName() );
                pstmt.setInt ( 2, dataEntryLot.getLotId() );

                pstmt.addBatch();

            }
            pstmt.executeBatch();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not Close selected DataEntry Lots", null );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( !success )
                    {
                        try { conn.close(); } catch( SQLException ignore ) {}
                    }
                }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }

        return success;
    }

    public static ArrayList<LabelValueBean> loadDepartmentList(DBTransaction dbTransaction) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<LabelValueBean> depList = new ArrayList<LabelValueBean>();

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT DISTINCT DEPARTMENT_NAME FROM Department_Table ");
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                depList.add(new LabelValueBean(rs.getString("DEPARTMENT_NAME"), rs.getString("DEPARTMENT_NAME") ));
            }
            depList.add(new LabelValueBean("--Add New Dept--", "-2") );
        }
        catch (SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not load Depatments", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                } catch (SQLException igonore) {
                }
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException igonore) {
                }
            }

            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException igonore) {
                }
            }
        }
        return depList;
    }
    public static void saveDepartments ( DBTransaction dbTransaction, String deptName, String fullAccountNo, boolean category ) throws Exception
    {
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success         = false;

    	try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
            pstmt = conn.prepareStatement( " INSERT INTO Department_Table (DEPARTMENT_NAME, Full_Account,Category) Values (?, ?, ?)" );
            pstmt.setString(1, deptName );
            pstmt.setString(2, fullAccountNo );
            pstmt.setString(3, category ? "PROD" : "ENGG" );


            pstmt.executeUpdate();
            success = true;

        }
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Tranfer material Departments", sqle);
		}
		finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}

			if ( pstmt != null )
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
    }
    public static boolean isDepartmentExists ( DBTransaction dbTransaction, String deptName) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        boolean isDepartmentExists = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }


            String query = "SELECT DEPARTMENT_NAME FROM Department_Table WHERE DEPARTMENT_NAME = ? ";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, deptName);
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                isDepartmentExists = true;
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Department name from database", sqle);
        }

        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return isDepartmentExists;
    }

    public static String getDepartCategory ( DBTransaction dbTransaction, String deptName) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String depmtCategory = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }


            String query = "SELECT CATEGORY FROM Department_Table WHERE DEPARTMENT_NAME = ? ";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, deptName);
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                depmtCategory = rs.getString("CATEGORY");
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Department Category from database", sqle);
        }

        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return depmtCategory;
    }
    
    public static void savePreGrindDisposeInfo( DBTransaction dbTransaction, InventoryTransitForm inventroyTransitForm, String userName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        LotDetails invTransitLots = null;

        ArrayList inventroyTransitLotsList =  inventroyTransitForm.getWipLotsList();

        if ( ( inventroyTransitLotsList == null) || (inventroyTransitLotsList.isEmpty() ) )
        {
            return;
        }

        try
        {   // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("{ Call SP_CREATE_UPDATE_PREGRIND_DISPOSE_LOT( ?, ?, ?, ?, ?, ? ) } ");
               
            for (int i = 0; i < inventroyTransitLotsList.size(); i++)
            {
                invTransitLots = (LotDetails) inventroyTransitLotsList.get(i);

                if ( invTransitLots.isChecked() )
                {
                    pstmt.setString(1, invTransitLots.getLotId() );
                    pstmt.setString(2, invTransitLots.getSplitLotOneWaferIds() );
                    pstmt.setString(3, invTransitLots.getSplitLotTwoWaferIds() );
                    pstmt.setString(4, invTransitLots.getWaferIds() );
                    pstmt.setString(5, invTransitLots.getNextLocation() );
                    pstmt.setString(6, userName );

                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Save PreGrind dispose Inventory Lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    private static Logger s_logger = null;

    private static void log (String message) {

        if (message == null) return;

        if (s_logger != null) {
            synchronized (s_logger) {
                // changed by hareesh on 13th January 2004
                //s_logger.debug (message + LINE_SEPARATOR);
                s_logger.logDebug (message + LINE_SEPARATOR);
            }
        }
        else {
            System.out.println (message);
        }
    }

    public static Logger getS_logger() {
        return s_logger;
    }
}
